Date query help

[subwoofer]

Hello all

I have a selection of different types of dates for a particular individual i.e. date1, date2, date3, date4 etc all contained in different tables...I need to ensure that all the diff dates are all within a week of one another and if not produce output so that I can investigate further.

Is there any way to get then compare the max date value with the min date value and then do the check? Or any other suggestions? The dates are not stored under the same variable name all have different names.

Any help will be v much appreciated!

sw.

 

[JamesMcS]

So each date is in a different table? And you have the individual's ID in each table too yeah?

Create a query, showing all the tables with dates and also the table with all individual's IDs (let's call it the main table). Inner join the main table IDs to the date tables IDs. Drag down main table ID and then the dates from the other tables. That will show you all the dates for each ID.

Which dates do you want to compare? You can easily add calculated fields into the query above to subtract one date from another and put a criteria in of >7.

 

[subwoofer]

Hi JamesMcS

All the dates (there are ABOUT 15) may be in different tables. The individual's ID is contained in every table. So for example date1 may be in tbl1, date 2 in tbl2 but date3 and date4 will be in tbl3.

I know I can compare it all to one variable to see if its within 7 days of that var, but was wondering if there was a simpler less messier way of doing so???...

 

[JamesMcS]

Interesting layout! Is there a way to have just the one dates table? It might make life easier in the long run....

 

[subwoofer]

Nope. Cannot change the layout - its the way the application is designed...I didnt design it!

 

[JamesMcS]

Hmmm.... can you UL a stripped out copy (no personal details etc.)?

 

[subwoofer]

Umm...althouth there is no data in the database atm...all the other details that are going into the database must remain confendential as it gives away other info...its complicated!

 

[JamesMcS]

Bit stuck without seeing it... try the query above, see what happens - even if there's more than one date it should come out with something, then we can build on that

 

[subwoofer]

Hi James

Thanks....Im going to get on with the rest of the queries that need to be put in place and come back to this. Will let u know how it goes, or if I run into any problems.

Thanks for trying to help - much appreciated!

 

[boblarson]

Union query -

Select IDField, Date1 As MyDate, "Table1Name" As TableName From TableName
UNION
Select IDField, Date2, "Table2Name" From Table2Name
UNION
Select IDField, Date3, "Table2Name" From Table2Name
UNION
Select IDField, Date4, "Table3Name" From Table3Name

And so on.

Then you have one date field you can compare against along with the user's ID.