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
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