transposing

[Amit112]

Hi everyone,
I was wondering if there was a way to query to combine many tables with one. For example

I have a workbook with 10 sheets all having columns labelled:
Date Date Due Date Closed Source and so on....

I want them all to filter out into:
Past Due Due in 1 week Due in 2 weeks

So all the sheets are labelled in the same workbook. I just want to run one query instead of 10 and have them filter out everytime it is refreshed


Any help would be greatly appreaciated.
Thanks

 

[Russell Hauf]

You could use a Union query for this. Look it up in Help or on the internet if you need to, but basically you'll need to go to your query's SQL view and type it in (mostly cut and paste) yourself. So if your original query is (I know yours is more complicated, but this should give you the idea):

SELECT FIELD1 FROM SHEET1 WHERE [DATE DUE] < Date();

Then your union query would look like this:

SELECT FIELD1 FROM SHEET1 WHERE [DATE DUE] < Date()
UNION
SELECT FIELD1 FROM SHEET2 WHERE [DATE DUE] < Date()
UNION
SELECT FIELD1 FROM SHEET3 WHERE [DATE DUE] < Date()
etc.

Hope this helps,
Russell

 

[Amit112]

Thank you it should help ray:

 

[Russell Hauf]

You're most welcome. And don't forget the semi-colon at the end of all those unions!