sql in access - combining select statements

[diverdave]

I am trying to generate a report based on data in a single table. Access does not allow you to bind 2 recordsets to a report without a subreport. This makes aligning data difficult and inneficiant.

problem:
one select statement with a where clause to extract data about a single customer. another select statement to gather data on all customers in the database. grouped and ordered by year (calculations are being performed in each select) the select output looks like this

first select has a where clause narrowing down the results...
Select sum(data1/data2) as Total1, sum(data3/data4) as Total2 from MyTable where CustomerID = 34;

output...
year Total1 Total2
2000 100 100
2001 200 200
2002 300 300


second select has NO where clause and gathers all data...
Select sum(data1/data2) as Total3, sum(data3/data4) as Total4 from MyTable;

output...
year Total3 Total4
2000 300 300
2001 400 400
2002 500 500


I would like the 2 select statement combined into one so I can get a single recordset, so that I can set the report records source to it. The new recordset should look like this so I can show a comparison between 1 customer and all customers...

desired output to show a comparison...
year Total1 Total2 Total3 Total4
2000 100 100 300 300
2001 200 200 400 400
2002 300 300 500 500

I seem to be unable to do this without creating a temp table and placing all data from both selects into the temp table and binding the recordsource for the report to the temp table. This poses a problem in a multiuser environment.

any ideas or suggestions

 

[Kozia]

If you are doing this in a query. Create query for the firs and a next query for the second select. create a third query and add the two above joining them on year. Select all from first and all from second. Or in VBA same way.

 

[blakezx1]

Dave, in looking at your result sets it appears you have one field in common that you can use to your advantage. That is the YEAR field. If you join your two tables on the YEAR field, then you can work with the fields from both tables all in one query and the results for each year will be displayed on a single row - just as you have described.

I've renamed your Table #1 to MyTableCUST
and your Table #2 to MyTableAll
The join statement is as follows:

Select
sum(data1.MyTableCUST/data2.MyTableCUST) as Total1,
sum(data3.MyTableCUST/data4.MyTableCUST) as Total2,
sum(data1.MytableALL/data2.MytableALL) as Total3,
sum(data3.MytableALL/data4.MytableALL) as Total4
From MyTableCUST LEFT OUTER JOIN MyTableALL ON
Year.MYTableCUST = Year.MYTableALL
where CustomerID.MyTableCUST = 34;

I hope this gets you going in the right direction.
Blake