Considering designing a project manager in Access. Is it worth it?

[shariq1989]

So here is the thing:




I started an engineering internship with a tug and barge company this summer. My boss wanted me to build him a vessel maintenance management system using which he could


1) Enter records of maintenance completed
2) Enter records of regulatory inspections completed
3) View a history of maintenance and inspections completed
4) View all upcoming maintenance and inspections, tiered by priority.
5) An alert function for tasks upcoming within a given time duration
6) A way to print a report for work history (3) and a worklist (4)
7) There would be a separate workbook for each vessel. Easily add new components or inspections to book for it to track.
8) Easily copy and modify the workbook to work with a new vessel


I am a really computer savvy guy but had little to no knowledge of programming or macros. I do have a really good ability to pick up new computer skills quickly to get work done. As long as I have a defined problem, I can come up with solutions. It took me a little over two weeks to figure out how VBA works with Excel and build a system that does everything mentioned above really well. The maintenance and inspections work off of either time or engine hours. I figured out a way for the worksheet to pull the engine hours from another workbook that gets updated with running hours every month. Using "Hours to Go" till an overhaul or inspection is due and the average number of hours an engine runs over the course of a month, the workbook can predict when a new inspection or maintenance is coming up. All these inspections and maintenances are compiled into a sheet where they are prioritized using conditional formatting. Once an inspection or maint. is completed, my boss will select it from a drop down and hit a button which will paste the entry with a time stamp into a Work History sheet and at the same time, reset the Upcoming Work sheet so that the component or inspection gets an updated due date (satisfying 1, 2, 3 and 4). Then I figured out a way for Excel to send out an email using Outlook once a component or inspection fell within a certain "Days to Go" range (5). The email would contain the name of the vessel, the component or inspection that needs attention and the number of Days to Go. Currently working on figuring out how to have Excel automatically add the due dates to the Outlook Calendar. I am also in the process of streamlining 6,7 and 8, and just making the workbook robust in general. My boss is highly satisfied with this current system I built but I want to make it better. I want to explore all the possibilities.


I have two more months to go on the internship and I have just now started looking at MS Access. The program just seems to be a lot more clean-cut and has the same VBA capabilities. I am wondering if it would be worth it for me to move this whole program into Access or if it is a total crap shoot. As it stands right now, I know nothing about the program but I can learn quick and am pretty interested in learning about databases. I have already started playing around in Access, just exploring what it is capable of. So it is time for me to reach out to you Access gurus! Is it worth it? Are there any better options?

[June7]

Access is the more appropriate software for maintaining data. Excel is great for intense calculations and what-if analysis but for data management, a relational database is preferable. Can you produce a workable product in 2 months? Maybe. Anyone who can figure out VBA coding behind Excel on their own in only 2 weeks shows promise.

The Excel will probably work fine for quite some time but can get significantly slow with a lot of data.

[shariq1989]

Thank you very much! I have decided to embark on my Access journey. My boss sees the benefit of Access too, we just hope that I'll be able to grasp concepts like Normalization and getting the UI working just right before its too late. I need to get this done and polished by July 31st and I am willing to put in all the time it will take. I want the chances of any issues coming up once I leave to be extremely minimal. But I think its a lot better this way than to have him accidentally mess something up in the Excel Workbook and not be able to figure out what happened. I think its a lot easier to make the Access interface foolproof since no one is going to be working directly in designer view. So for now I just need to go back to the drawing board and write everything out, make a plan for the tables and forms needed... and go from there?

[June7]

Yes, sounds like you will do well. Might review this tutorial thread http://forums.aspfree.com/microsoft-...es-208217.html

Just keep in mind nothing is ever totally foolproof - a better fool will always come along and prove you wrong!

[ssanfu]

Another site for Access basics is:

http://www.accessmvp.com/Strive4Peace/

I also think you are headed in the right direction

[orange]

If it's a learning exercise and limited use tool, then go for it. If you have Office products, have you considered Microsoft Project?
I would look at supported open source project management software that has some user forum.
I found this list via Google.
http://www.techsupportalert.com/best...ct-manager.htm

I do not use any of these products and I am not associated with any of these products.

[shariq1989]

So i did a lot of reading on database theory and worked on getting a good understanding of concepts like normalization, etc. This is the schema I came up with based on what I read. I kind of ignored the "how to get to where I need to be" part and just made tables that will hold all the information the system will need, hoping that that is something that will be worked out eventually. Does this look like something that will get me all my end goals?

..And those project managers look like they might work too. I will look into that.

[June7]

Looks good. Programming is not required to have a functioning database (I have a couple with zero coding - no macros, no VBA) but if/when you get to a point you feel code is needed, review: http://office.microsoft.com/en-us/ac...010341717.aspx

[shariq1989]

Wow, its pretty crazy for me to picture that Access will be able to perform all the things I need (calculating the new due date and sending out alerts) without coding. That sounds awesome though. So next up.. I should start building these tables and setting up relationships in Access... and then start with forms?

[June7]

Yes, I think the automation you describe will need code. You just have to decide which way to go. I don't use macros unless absolutely have to (twice), only VBA, but web-based (using Sharepoint) db cannot use VBA. I also avoid the programming wizards. I have never used the Switchboard wizard.

Time to build.

[shariq1989]

I'll probably just use VBA. Working on setting up relationship and populating tables right now.

[shariq1989]

One question I have right now is that I cant visualize how this will work.. So there will be many many inspections in the Inspections table but not all apply to each vessel. It varies by vessel. How is the application going to know which inspection needs to be done on a certain date on a certain vessel and then alert likewise?

[June7]

Could have another table that associates each vessel with all possible inspections for that vessel. Then query can join this master with PastInspections (or a query that shows only the latest of each inspection for each vessel) to see if and when inspection was done. The join type would be 'Show all records from Master ...' If the PastInspections side of the join is blank then you know that inspection never conducted. Include Inspections table to get the Interval value to use in date calculations.

Is the interval for an inspection the same regardless of the vessel?

[shariq1989]

The intervals for each inspection will be the same regardless of vessel, yes. I guess I am just having trouble visualizing the new table that would contain information on which vessel needs which inspections. I guess VesselID and InspectionID would be all it would need, huh?

[shariq1989]

So I created a new table called VesselInspections, where I have VesselID and InspectionID and I populated it with all the vessels and the corresponding inspections that they need. Now I am currently filling out the Inspection History table. I got rid of the VesselID and InspectionID columns in InspectionHistory and just used the PK for VesselInspections to refer to a certain inspection on a certain boat. Getting a bit frustrating to have to look up, VesselID and InspectionID to find the corresponding VesselInspectionID so that I can enter it into the InspectionHistory table...