max function with combined tables

[Ramesh]

HI,

If i have a date field in 2 tables and i need to get the latest date from
the combination (union) of both tables, how can i do that? For the same
name.

eg i have 2 orders table and i need to get the latest order taking both
tables into account.

any help please. i ve been struggling with this for a while now.

thanks
Ramesh

 

[Guest]

Hi

Create a union query of both tables including as many fields as you need.

Eg Query1

select field1, field2, mydate from table1
union
select field1, field2 mydate from table2

then write a query against Query1 to find the max date

select max(mydate) from Query1

Be careful to put the columns in the correct order to match each part of the
union query.

Regards

Andy Hull

 

[Ramesh]

Thanks very much..

i completely forgot about the union query .. i had just read up on it a few
weeks ago.

Ramesh