Latest Date from 3 tables

[ccg_0004]

Hi all-

I have searched for a while and have seen some suggestions of using Max date queries and others. I have not found any solutions that I can implement into my DB. I am posting a copy of my DB in access 07.

I want to return the latest known location for all tools in the tbl_tools. The query should check tbl_repair and tbl_invoice and retrieve the latest date FOR EACH TOOL NUMBER. results would look as follows:

tool# Date ID
1234 5/1/08 4 (from repair table)
1235 2/2/08 4 (from invoice table)
1236 3/2/08 5 (from invoice table)

I know how to get the latest date for a single tool, but not for all the tools in the tools table. thanks for any help

CG

 

[ccg_0004]

OF course..... figured it out

I figured it out of course shortly after I posted. I can post the solution if anyone likes.

cg

 

[datAdrenaline]

Honestly, it sounds like your tables are not designed properly .... what do you mean when you say the latest "Location" ... do you DELETE a record from one table, and subsequently ADD a record to another? ... Rest assured what you seek can be accomplished, but this might be a good time to analyze your structure to ensure proper normalization.

 

[datAdrenaline]

Sure ... I'd love to see it ... curious if our approaches are/were the same! ... I would end up using a UNION query that is grouped and joined to tbl_tools ... At least that is the way I envision it ... but sometimes as we develop, we see new ways!! ... but even though you developed a solution, I may be good to consider your structure.

 

[ccg_0004]

Solution Posted

datAdrenaline- I"m sure you're right that my table config could use some tweaking. i'm not sure where however.

The solution was a two part query- the first being a union query called "union_noparam". The second part of the query is called "Tool history". running this query gives me the exact results i need.

CG
ps- let me know if you notice anything in my table setup