Design assistance (re date management) needed!

[Pienuts]

Hi all!
I am working on an inspection DB at work and I am having difficulty figuring out the best way to deal with dates for future inspections and to be able to have the data easily queried to find out what inspections are due.

In a nutshell:
1. sites need to be inspected 2, 5, 10 and every 10 years after construction;

2. if an issue is found on a site, the inspector chooses a re-inspection date to reassess the issue;

3. if there is an Environmental Incident Report (EIR) done on a site, the reviewer chooses a re-inspection date to monitor recover; and

4. if a new site is constructed, it needs to be inspected within a week of the reported construction date.

The data on issues is in tblIssue, EIRs in tblEIR, and new site construction in tblNotification. I originally thought that the re-inspection dates should be in their respective tables, but I couldn't figure out how to query them and also get the scheduled (2yr,5yr,10yr,etc) dates to pull from.

Also of note: there are over 20,000 sites in my DB.

Could someone assist me with finding the best method to store / recall these dates? I would be happy to provide any more detail if necessary, I just don't want to throw any irrelevance into the mix!

Thanks!

 

[Fran Lombard]

I think one table should suffice. Table Inspections
This table would have at least the following fields:

InspectionID- PK- Autonumber
SiteID-FK-Long
InspectionType-FK-long
ScheduledInspectionDate-Date

And any other fields you need to schedule an inspection.
You could also add any fields you need to record the results of the inspection.
If the different inspection types require vastly different fields those fields can be in seperate tables as needed.

Hope this helps

 

[Pienuts]

Thanks for responding, Fran!
That’s basically the structure I was trying for a time, except I had it as a separate table from the data generated from each inspection. My reasoning was that not all inspections have a due date (some are just random site visits), and the EIR and new construction data are completely different and separate from the other inspection data. My old structure almost worked fine; I had any re-inspection dates entered into forms populate the SiteID, the InspectionType, and the ReinspectionDate. The problem I was running into was the “scheduled” inspections. I couldn’t figure out the best way to generate the next inspection due for any new sites, or how to get it to realize that one inspection was done (or skipped) and to generate the next inspection due date.
There was also the issue of management – with there being so many sites, any inspection that had already been completed or was not the next scheduled inspection doesn’t really need to exist in that table (the actual inspection data table has a date field for when it was completed), so it could either be updated with a new inspection date/type, or deleted and a new entry created.
Have I been thinking about this the wrong way? For the life of me, I can’t figure it out.
:-/

 

[Fran Lombard]

I'm a little lost trying to understand what the current issue you are having.

I would think that all inspections have some common data elements such as:
SiteID
InspectionType
ScheduledInspectionDate
ActualInspectionDate
InspectionStatus
.....

I would try to have have one master Inspection table with a one record per inspection, whether planned, random or passed and separate tables to hold the results of these inspections by type to the extent they were different. Any other common fields whether related to the scheduling or results should also be held in the master table.

Additionally, I would not delete any records for passed inspections even if they never happened. I would handle those through some form of status.

With this approach, all your queries would drive off the master Inspection table with joins to the separate InspectionTypeResults tables using the InspectionID from the master Inspection table as the joining criteria.

Does this make sense?

 

[Pienuts]

I do understand what you're saying, and there are common elements to all inspections; my inspection table does combine these.
I'm still not sure that this is where my next inspection dates should go; perhaps my problem is that there are an infinite amount of scheduled inspections(every ten years forever). I'm sorry if I'm missing something obvious.
I've been working on a seperate tactic today - I made a query which only returns the next scheduled inspection type and date for each SiteID, and I'm thinking of combining it with the user-produced next inspection date (from Issues or New Construction Notifications or EIRs) to create an all-encompassing future inspections query. I could add a "completed" field for filtering. Do you think that may work?

 

[The_Doc_Man]

I'm still not sure that this is where my next inspection dates should go; perhaps my problem is that there are an infinite amount of scheduled inspections(every ten years forever). I'm sorry if I'm missing something obvious.

What you might be missing is that each inspection has a separate entry in your table of inspections. If you visit the site 4 times, you have four entries. Which means that the primary key can't be some combo that omits the date, unless you use an autonumber PK that has nothing to do with the dates at all.

You would have two states for an inspection - completed or scheduled - if your prefer, past and future. An ancillary datum would be the estimated time needed until the next inspection, which you explained depends on whether you are doing time-based or event-based inspections. Perhaps you might consider building your inspection entries based on a scan of the inspections in the past that looks at the most recent date and most recent code to decide when to define the next inspection record. For any scan that shows the highest date in the future, don't schedule anything. If your highest date is in the past, you can schedule something based on the past date and the code that you use to recommend whether the next inspection is time-based or a follow-up to an event.

Does that help orient your thoughts or did I take a tangent on you?

 

[Pienuts]

Okay... thoughts slowly orienting...

Your first para confuses me a bit. The inspection table is currently set up just as you said - if you visit the site 4 times, you have four entries. My inspection table PK is an autonumber field, and I don't understand why you said "the primary key can't be some combo that omits the date". That being said, I don't think my comprehension of that line affects my understanding of the "gist"!

So if I understand correctly, my approach should be to:
1. add an InspectionState ("Complete","Scheduled") field to tblInspection which would change to "Complete" when an inspection form is completed;
2. add a InspectionDueDate field to tblInspection (right?); and
3. write some VBA that adds new entries to the table when the previous entries for a site are either changed to "Complete", or the InspectionDueDate has passed.

Sorry I'm being so slow - this DB took a turn that I wasn't expecting during the initial development. Am I looking in the right direction now?

 

[Pienuts]

I have implemented your suggestions and everything is working well! Thanks to both of you for your assisstance!