Looking in 3 tables for one result

[matpj]

I have 3 project tables, which record time boked against projects:

Table1: Archive
Table2: Transitional
Table3: Current

when time is saved in the time-keeping system it goes into the transitional table, and then at the end of the month it is put in the Current table.

I need to write a report that shows the last time that a project was booked to.
To do this I will need to look in all 3 tables.
How could I do this.

Alol tables share the same structure.
and the fields I require are simply ProjectCode and TransDate

each table will have many rows for each project (each row recording a work transaction)

any ideas?
My report should simply show the following:

ProjectCode, Latest Transdate, DurationSince (calulated)


thanks in advance,
Matt

 

[KenReay]

Hi

Make a union query

SELECT * FROM Archive
UNION
SELECT * FROM Transitional
UNION
SELECT * FROM Current

then use the resulting query in your search

SELECT * FROM MyUnionQuery WHERE ...


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[matpj]

Hi Ken,

thanks for the suggestion.

do you know if its possible to combine the queries into 1?
or do I have to have a two stage process?

 

[KenReay]

hi

"I need to write a report that shows the last time that a project was booked to.
To do this I will need to look in all 3 tables.
How could I do this."

how about

SELECT * FROM Archive WHERE ..whatever
UNION
SELECT * FROM Transitional WHERE ..whatever
UNION
SELECT * FROM Current WHERE ..whatever





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK