Determine Most Recent DateModified from multiple tables 3

[sterlecki]

I would like to determine the most recent datemodified value from a series of tables that are used in my db. I would then like to display this date on a report so that at report time the user would know when the last modification took place.

All my tables are related with the PK of API. Some are 1 to many relationships. All tablenames are prefixed with "data_sometablename".

There are probably 20 tables.

I was thinking of some sort of nested loop where for each table in the DB with a name Like(data_*)select the API and datemodified fields,
Store the date value and in the case of a many type table find another record and compare the dates
store the most recent date then
Next Table
finally spit out the most recent datemodified value.

Or
Roll through all the Like(data_*) tables and collect the table name, API and datemodified
Store them in an array and find the most recent.

Any thoughts on the best way between the two methods or any other suggestions?

 

[PWise]

how about
Select max(maxdate)
from (
select max(datemodified) maxdate
from table1
Union
select max(datemodified)
from table2
Union
select max(datemodified)
from table3)maxdates

 

[SkipVought]

There are probably 20 tables.

I was thinking of some sort of nested loop where for each table in the DB with a name Like(data_*)...

Hmmmmmmmm???

Do these tables contain similar data [highlight]DB with a name Like (data_*)[/highlight]?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sterlecki]

Hello PWwise

I've got this going but it has an error perhaps you can spot it.

SELECTmax(MaxOfDateModified)
FROM(
SELECT data_WellHeader.API, Max(data_WellHeader.DateModified) AS MaxOfDateModified
FROM data_WellHeader
GROUP BY data_WellHeader.API
HAVING (((data_WellHeader.API)="49035278970000"))
UNION ALL
SELECT data_FormationTops.API, Max(data_FormationTops.DateModified) AS MaxOfDateModified
FROM data_FormationTops
GROUP BY data_FormationTops.API
HAVING (((data_FormationTops.API)="49035278970000"))MaxOfDateModified;

SkipVought

I have prefixed my significant tables for differentiation to have a naming convention:

data_WellHeader
data_FormationTops
data_WellContacts etc

Other tables are prefixed as lkup_ for lookup tables
or audit_ for tracking users looging on and off.

Each of the data_ tables have a PK of API. The WellHeader table is at the hub of all other tables. A change of API in this table cascades to nearly everyother table that is associated with a particular well.

Currently in my report the DateModified field only refers to a record change on the WellHeader table. Users want to be aware of the last modification made on any table used in compiling the report. I might even add the tablename of the DateModified field for clarity.

Hope this helps.

 

[Gammachaser]

If I understand what you are looking for, you want the date the table was modified, not data from a record in the table. You can get that from the MSysObjects Table (which is hidden but available) like this:

DMax("DateUpdate", "MSysObjects", "Name Like 'data_*'")

That code will pick out which of the tables that start with 'data_' was modified most recently ASSUMING that only tables are named starting with 'data_' -- you have to be careful that you don't have a query (or form or other object) that starts with 'data_' or they will be included.

Personally, I start my table names with 'tbl' so that I can be sure I get a table and not a query if I use similar names. For instance I could have a tblUsers and a qryUsers... both give access to data, but I can identify which is which very easily.

 

[Gammachaser]

I guess I was wrong in my assumption of what you were looking for, since your code is pulling data from the table and not about the table. DateModified usually refers to a modification made to the table structure, not data.

Sorry for sticking my nose in.

 

[sterlecki]

Gammachaser

Not at all. As I mentioned once I get the date out that I want I might want to add the name of the table that it belongs to.

I was able to get some sql running from PWwise's suggestion.

What I have for two tables WellHeader and FormationTops which are in a one to many relationship is this:

SELECT Max(MaxOfDateModified) AS MaxDate
FROM (SELECT data_WellHeader.API, Max(data_WellHeader.DateModified) AS MaxOfDateModified
FROM data_WellHeader
GROUP BY data_WellHeader.API
HAVING (((data_WellHeader.API)="49035278970000"))
UNION ALL
SELECT data_FormationTops.API, Max(data_FormationTops.DateModified) AS MaxOfDateModified
FROM data_FormationTops
GROUP BY data_FormationTops.API
HAVING (((data_FormationTops.API)="49035278970000")))  AS [MaxDate];

I just need to add the remaining tables and perhaps extract the tablename and filter it by the API(s) selected at report time.

 

[dhookom]

If I understand your needs, you are working way too hard.

SELECT "WellHeader" as SourceTable, Max(DateModified) AS MaxOfDateModified
FROM data_WellHeader
WHERE API="49035278970000"
UNION ALL
SELECT "FormationTops", Max(DateModified)
FROM data_FormationTops
WHERE API="49035278970000";

Duane
Hook'D on Access
MS Access MVP

 

[sterlecki]

dhookom,

This is what is returned from your query;

SourceTable MaxOfDateModified
WellHeader 6/6/2011 7:55:55 AM
FormationTops 11/11/2011 2:22:39 PM

What I want as the final result would be what's contained in the last line. FormationTops and 11/11/2011.

So how do I boil it down so it returns just 1 line?
Your method is less work.

 

[PHV]

What about this ?

SELECT TOP 1 * FROM (
SELECT 'WellHeader' AS SourceTable, Max(DateModified) AS MaxOfDateModified
  FROM data_WellHeader WHERE API='49035278970000'
UNION ALL SELECT 'FormationTops', Max(DateModified)
  FROM data_FormationTops WHERE API='49035278970000'
) U ORDER BY 2 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

thank you all for your suggestions.

The solution that I will use as a list box on my report is:

SELECT TOP 1 *
FROM (SELECT "WellHeader" as TableName, Format(Max(DateModified),'mm/dd/yyyy') AS  LastDateModified
FROM data_WellHeader
WHERE  API=[Reports]![rpt_WellHeader_Pinedale]![API] 
UNION ALL 
SELECT "FormationTops", Format(Max(DateModified),'mm/dd/yyyy')
FROM data_FormationTops
WHERE API=[Reports]![rpt_WellHeader_Pinedale]![API] 
UNION ALL 
SELECT "PermitsAndRegulatory", Format(Max(DateModified),'mm/dd/yyyy')
FROM data_PermitsAndRegulatory
WHERE API=[Reports]![rpt_WellHeader_Pinedale]![API] 
UNION ALL 
SELECT "LogEval", Format(Max(DateModified),'mm/dd/yyyy')
FROM data_LogEval
WHERE API=[Reports]![rpt_WellHeader_Pinedale]![API]
UNION ALL 
SELECT "LogEvalDetail", Format(Max(DateModified),'mm/dd/yyyy')
FROM data_LogEvalDetail
WHERE API=[Reports]![rpt_WellHeader_Pinedale]![API] 
UNION ALL 
SELECT "CasingAndCement", Format(Max(DateModified),'mm/dd/yyyy')
FROM data_CasingAndCement
WHERE API=[Reports]![rpt_WellHeader_Pinedale]![API] 
UNION ALL 
SELECT "MudProgram",Format(Max(DateModified),'mm/dd/yyyy')
FROM data_MudProgram
WHERE API=[Reports]![rpt_WellHeader_Pinedale]![API] 
)  AS U
ORDER BY 2 DESC;

Seemed to be the path of least resistance.