"Hello World!"

Haven't been back here since the undergrad senior project.

I'm now going to expand the mission statement of this blog from just a university project to general musings, and to add some cross-linking for personal branding.

I've just finished my Technology Management MBA at the University of Washington, and am actively seeking my next career opportunity.

My most recent resume

Eriel.com My Homepage

ESN Tech: My small IT consulting company

My personal site: for fun and hobbies and interests

My LinkedIn site

My WordPress site

Where I'm keeping my photos these days

Senior Project Demo'd and Documentation posted

I visited Dr. Griggs this afternoon and demonstrated the database application for final submittal. This benchmark concludes the work done for my senior project credit.

We went over the issues that I was confronted with throughout the duration of the project, showed the database tables and constraints and also the working front-end of the system. We discussed the areas where the business rules of the organization for which this database was designed caused the design to deviate from a perfect textbook example of a database management system.

I handed in the final paperwork and also posted it on my site at the CSU's server http://129.65.91.72/enash/

I'll be looking to enhance the design and functionality with the information gleaned from BUS496 next quarter by making the SQL database accessible via a .asp or .php interface.

Finally got my close button!

What a pain in the neck! Hours spent on the silliest thing.
Issue: wanted to have a button that would automatically set the close date to getDate() when tech claims ticket is resolved.

Why not just allow user to do it? Because I don't want them cheating and saying their turnaround time was better than it is. I also wanted the tech not to be able to modify the field.

Solutions:

1. Views in SQL? No. Would never refresh after the initial form was opened in Access
2. Stored procedure? No...well probably, but I don't know enough about SQL to do it right. I got it to work in that Access would pull up the stored procedure's results but not plug the data where I wanted it. The only way I could think of would be creating a parameter and passing the row number to modify only one cell, and then it went pear shaped.
   
3. Fix: created a macro, using the Access expression Now() and told it to fill in the close_date field and now I get a matching format to the dateTime values (I already set my SQL table to automatically use getDate() whenever a new record is created for the open_date) in the open_date.

Also, I added some new reports that look better than the user just looking at the raw query results.

In addition I got a reasonably advance query to work showing number of tickets assigned to the tech, and his/her average turn around. Somewhat tricky because I didn't want the numbers skewed by unassigned tickets, or tickets that weren't closed yet.

Business rules don't allow good database model

I did get the merge done between the ticket database and the software licensing database, but I found that the company has put a roadblock in my design plans. To track the software properly I need a unique asset tag (read primary key) on each machine and the corresponding database table would in turn have all the machine stats. However, no such infrastructure exists. To create it--getting to 5 remote offices, inventorying/tagging over 125 machines isn’t possible under my current demands at work (read they wouldn’t pay me to do this project, when they have other higher priority projects they have me on). I think I’m going to create the table anyway for future versions of the database, but for now, the machine will have to just loosely be identified (e.g. marketing Dell laptop, PowerEdge 2800 server, receptionist’s Compaq…).

Still having issues with the automatic close date for the ticket, for now I may have to allow the user to type in the data (only managers/IT staff will have access to it anyway).

Merging databases, triggers? Stored procedures? Growl

Well, now that the main database is up, normalized and working I thought I'd get ambitious and merge another database I'm doing for work. It's a software license tracking database, it would have a common table with this one anyway, "employee" and the current "employee_id" would be a nice foreign key to the IT Ticket's "employee" table. Really not a problem but I'll just have to hand code the data in again as that one was made just in Access. It would also let me migrate into the ticket database asset tags, and IP numbers. User could then further identify problem areas beyond location and add machine name/IP/asset tag....of course they'll NEVER bother to gather this data, so I'll have to allow null fields in the ticket.

The real issues I'm hitting is I want to do more that the Access front end is allowing me to do. And I don't know how to do VB.NET well enough to rework the front end by hand coding.

If I can get some triggers figured out, it'll be ok. I want to change the status to closed and have the database automatically input the datetime in the "date_closed" field.

Which brings up some of my other ideas, now that I've been working with a working BETA model the last couple weeks. I'm going to want to do a priority "low" "medium" "urgent," and also put a status "unassigned," "assigned," "closed" etc. Right now it just presumes the ticket is closed if there is a closed date, but maybe the queries will be be easier to program (date math is a hassle) if I just validate the "status" field.

Access -> SQL, Forms, Reports, Normalization

It's been a productive week, after resolving the SQL questions, I rebuilt all the tables that were in Access into SQL (currently 6 tables).

After looking at the tables for normalization issues, and not liking some of the layout, I took out a ticket details table (because SQL text 16 will allow massive amount of text). Now the design will be for a continuous text field for initial problem entry and subsequent tech update entries. This makes the layout nicer, but another reason for the change was that multiple entries into a ticket caused me troubles on the reporting side (multiple rows shown for only on ticket number due to the one-many relationship with ticket and tech update entries).

I'm still using Access .adp file to access the SQL, and have customized some of the queries in SQL Analyzer, some in Access, I had to recreate the forms, and I'm doing customized navigation menus.

With the core infrastructure in place and stable now, I'm going to be looking at restricting editing per logins, creating a more stand alone (as in not opening full Access) look to the app, create a master form for all data entry, query, reporting functions.

SQL issue resolved + updates

SQL autonumber issue was ridiculously simple when using the enterprise manager GUI to create the table (int/identity = yes/identity seed =1/identity increment = 1); hand coding it in a text file to create tables in the query analyzer wasn't working so hot. It looks like I'll use the GUI of the Enterprise Manager for the continuation of the project. So the next steps are to export my tables from Access or re-create them in SQL. SQL or an Oracle as the database is the mature way to design this project, and Access was going to be the easy way out. Plus I want this project to be able to grow, and creating it in a enterprise level database will allow for that; opening the door for .asp/.php or VB.NET programming.

I've signed up for BUS496 next quarter where I can get some .asp:database experience, but won't be able to utilize that skill for the graded portion of this project.