SQL Subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would like to selected all the records from Table1 (all stores) , all matching records from Table2 (jan sales), all matching records from Table3 (feb sales), etc.. to build a grid of sales by store. Usually do it 1 table at a time and paste the results together in Excel, by Selecting all records from table1 and any matching records from table2, then another query for table3, etc..

Any suggestions for a monster query to do it all in one shot

Thanks
Mik

Store Jan Feb Ma
1
2
3
4
5
 
Your problem is complicated by having separate tables for each month. You
can pull all the sales tables into one recordset with a UNION query:

SELECT Table2.Sales, "JAN" As SaleMonth
FROM Table2
UNION ALL
SELECT Table3.Sales, "FEB" As SaleMonth
FROM Table3
UNION ALL ... {include the remaining months desired}

Now you can join that union query with Table1 and build a Crosstab to get
the result you want.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Mike Klein said:
Would like to selected all the records from Table1 (all stores) , all
Click to expand...
matching records from Table2 (jan sales), all matching records from Table3
(feb sales), etc.. to build a grid of sales by store. Usually do it 1
table at a time and paste the results together in Excel, by Selecting all
records from table1 and any matching records from table2, then another query
for table3, etc..
 
Back
Top