Inspection Scheduler

[tracetrimble]

I wrote a db to manage projects (wastewater treatment industry) for my company, and I need to add the ability to schedule and report upcoming inspection dates for equipment that has been installed in the last 5 years (our typical warranty period). I can't find much that is helpful on this topic on the Google. We are required to do inspections at 3 months, 6 months, 1 year, and every 6 months after that out to 5 years after the date of installation, so there are 11 required inspections (for our typical warranty period). The dates can float around in that timeframe depending on availability of people, plant downtime, etc.



For now, I've just added 11 date fields to the table which captures most other data about the project, but I feel there is a more elegant solution out there. I would like for it to appear as a single date on the main form, showing only the next upcoming inspection date. If someone wants to review dates that past inspections occurred, that could be on a different form/report.

I also need to generate a report of inspections coming due in the next say 2 months, and I'm not sure how to do that. I am not strong on reports.

Any help or other ideas?

[Micron]

I've dabbled in a lot of PM and corrective data, both with fixed and variable frequency requirements and will say that for this, 11 date fields in a table is just not the way to go. The only difference between what I was working with and what you have is that for me, the frequency never changed. A basic requirement for you would be the last inspection date field and then a calculated due date. The difference between the due date and the executed date could provide for the percentage of work completed on time (can allow for a grace period if desired). I think in your case, if the count of PM executions is < 2 then the frequency is 3 months (if I understand what you've posted) else it is 6 months, up to a count of 11. Not sure why there's a cut-off point though, for surely it's possible that future economics could dictate that an asset doesn't get replaced after 5 years, or that a PM doesn't get performed because it is (e.g.) 2 weeks after the 5 year period.

Much of your information output is probably going to be best served up as reports rather than forms. Forms might expose data in a way that allows casual users to alter or even delete data.

[tracetrimble]

Thanks. I think you are picking up what I'm laying down here. I'm absorbing your advice, but just a quick response to one point: we don't do inspections after 5 years because the customer's warranty is expired at that point. We try to help our customers meet their warranty obligations to the equipment manufacturer. If they miss an inspection, they could be out six figures if something goes wrong with the equipment later.

The warranty period can be 1 to 10 years, so 11 hard inspection dates is not a good solution. It's just all I'm capable of right now.

We are a small group of highly competent people, but I'm the only Access guy. I'm not too worried about data corruption from inexperienced users at this point, but it is always something to consider. I am self taught and not strong on reports and other areas (obviously), but willing to learn.

[Micron]

If you haven't studied db normalization yet, it's a must to do so before you dive in - and get a good grasp of it. If you have Excel experience, you must forget everything you know about it, because you definitely should not design tables as you would spreadsheets. There's a fair bit of calculation done in a db like this, but all of it should be done on the fly in forms and reports - not in tables. A PM due date is calculated by adding the frequency to the last performed date, so there you have at least 2 data fields that are required.
I recommend these topics for novices.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...594468763.html