Finding MAX Date from 4 Different Queries in MS Access

I have 4 queries that pulls 4 different date fields:

Query 1 - Date1
Query 2 - Date2
Query 3 - Date3
Query 4 - Date4

All have a common field of FILENUM

I am trying to incorporate the most recent date from all 4 of these queries into a final query that will place the most recent date from the 4 queries into 1 field on the output MAX_FieldDate.

If Date1 = 01/01/2010 and Date2 = 01/01/2011 and Date3 = 01/01/2012 and Date4 = 01/01/2013 I want my final query to find Date4 and return it in a field in my final query. I understand I may need to make additional tables or possibly a module but not sure and do not want to bang my head against the wall for too long on this.

Thanks in advance for the assistance...
sparker1970Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You can use a union query to get all four queries into a single recordset and use the Max() function to get the date you want.

qUnion
Select * from q1
Union Select * from q2
Union Select * from q3
Union Select * from q4

qMax
Select Max(yourdate) From qUnion

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
That will be:

Select Max([Date]) From
(Select Date1 As [Date] From Query1
Union
Select Date2 As [Date] From Query2
Union
Select Date3 As [Date] From Query3
Union
Select Date4 As [Date] From Query4)

/gustav
sparker1970Author Commented:
Totally forgot about Union Queries.

I implemented a Union Query and then MAX queried that for the most recent date. Worked perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.