Returning the lowest date from a multitude of sources (1 Viewer)

[Pienuts]

Hey, all!
I'm a bit stumped with a development question -

I am doing an inspection DB and there are a few different kinds of inspections (some with their own tables). Most of these types of inspections have a "re-inspect date" which will be used for alerts and reporting processes.

What I am trying to do is find the most elegant way to return the next inspection date for any given site. I started a query and got as far as having a row per site with the dates in 14 different inspection categories, (most of which are calculated - we do inspections after 2,5,10 years and every ten years after construction) but cannot figure out a way to pull the earliest date to a 15th column, NextInspectionDate.

It's quite likely I am looking at this the wrong way, but I'll be damned if I can figure out the right one!

Thanks for your help!

 

[Galaxiom]

Such requirements usually indicate a data structure issue. All those dates should be in the same field in the same table. Then it would be easy to get the result. I would strongly recommend you amend the structure or you will continue to run into messy queries.

However if you want to persist without fixing the error you can use a Union query.

 

[Pienuts]

Okay, thanks for the reply!
So what I'm getting is that there should be a table with
SITEID | InspectionType | InspectionDate | ???

Forgive me, there's a reason this wasn't built in -it seems to be beyond my comprehension!
I can figure out the majority of population of this table - I'll just use the inspection forms - but how can I get it to populate the 2, 5, 10, 20, 30...year inspection dates?
Also, when it's not for a regular scheduled inspection, I'm thinking there would need to be a foreign key used, but this will be from three tables - what is the best way to do that?
Thanks for your help!

 

[Pienuts]

Okay, well I created a new table with:
PK | SITEID | Inspectiontype | InspectionDate | FK1 | FK2 | FK3

I exported my original query into Excel and manipulated it to get the structure right and imported it back in. I think I have it sorted!

My only problem is that I will have to do the export/manipulate/import every time a new site is added to the DB - multiple times a week. :-/

So... If anyone has any bright ideas on how I can do this more efficiently, that would be great!

 

[namliam]

why would you need the FK1,2,3? Using numbered columns is usually a bad idea as well as using multiple date columns

Why would you import/export to/from excel to manipulate your data?
Some smart queries or at worst some clever VBA should be able to manipulate your data for you so you can have a proper design and have your data populated automagicaly....
Then again, if these dates are all calculated, wouldnt it be smarter not to store the dates at all, maybe untill you actually need them.

Untill such time that you need them, simply calculate the calculated dates on the fly?

 

[Pienuts]

Thanks for replying, namliam!
I didn't actually name the foreign keys FK1, FK2... - I just didn't think it mattered enough to specify!
So what I did was create a new table, and put all new re-inspection dates into it instead of in the seperate inspection tables. I used the foreign keys to link to the inspections in question. As far as the 2,5,10... year inspections, I have changed it so that only the next inspection date is in the table - when a new inspection form is saved, the date for the site will change to the next scheduled one. Now I just have to build the code into the forms!
So, after much too long, I believe I've finally found the best way.

 

[namliam]

why create a FK for each seperate inspection?
You have a record per inspection, you should only need one FK to link to the "right" inspection?

 

[Pienuts]

Actually, I've got inspection dates coming from multiple situations. They can be generated from an issue found at an inspection (tblIssues, or from a notice of a new site's construction (tblConstructionNotice), or from an Environmental Incident Report (tblEIR). So, I need to be able to link to all three from tblNextInspection.
Do you think there is a better way?

 

[namliam]

Assuming each of the 3 tables have a unique key between them, i.e. a key in tblEIR can never exist in tblIssues you only need one FK to link to all 3 tables.

Worse yet, if the situation tables are simular you should only need one table instead of 3...

 

[Pienuts]

Well, they're definitely not similar, but you've got me thinking on the unique key in one FK - there's nothing yet, but would you suggest creating unique keys that aren't Autonumber? I was under the impression that wasn't preferred.

 

[namliam]

You can make any unique key you want, preferably it will be a LONG column like an auto number. However you can for example make your own incrementing key.

I.e. make a table lets call it tblKEY, this table has only one column and will only ever have one row. Then use a function of either one below to get the "autonumber"

Function GetNewKey() As Long
    GetNewKey = Nz(DMax("KeyColumn", "tblKey"), 0) + 1
    CurrentDb.Execute "UPDATE tblKey SET tblKey.KeyColumn = " & GetNewKey
End Function
Function GetNewKey2() As Long
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblKey")
    GetNewKey2 = rs!KeyColumn + 1
    rs.Edit
    rs!KeyColumn = GetNewKey2
    rs.Update
    rs.Close
    Set rs = Nothing
End Function

 

[Pienuts]

Very nice, namliam - that's awesome! I am going to save that for later, because I've come to the conclusion that I need to re-design the whole "re-inspection date" aspect. I'm going to mark this thread as solved and start a new one.
Thanks for your help!