Combining Reports

[Guest]

I have three queries one for finding all retail sales, all credit card sales, and all house charge sales. I run three seperate reports and display the total sales value at the bottom for total sales. I want to create a report that displays all three values in it. How can I do this.

 

[Allen Browne]

The answer will depend on your table structure.

Ideally, all your sales will be in the one table, with a field that
indicates what type of sale it is (retail, or card, or house). With this
structure, the report you ask for is very simple. Just create a report, and
in the report's Sorting And Grouping dialog, group by Sale Type. With a
Group Footer for each one, you get your subtotals, along with the grand
total in the Report Footer.

If you have your amounts in different tables and this can't be changed, you
may be able to create a UNION query to combine them all into one, and then
base the report on that as discussed above.

If that's not possible, the other alternative is to create 3 reports (one
for each). Then create another report that contains these as subreports. You
can bring back the total from the Report Footer section in the subreport
back onto the main report with a text box that has a Control Source of
something like this:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report]![MyTotal], 0), 0)
where "MySub" is the name of your subreport control, and "MyTotal" is the
name of the text box in the subreport's Report Footer section.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have three queries one for finding all retail sales, all credit card

sales, and all house charge sales. I run three seperate reports and display
the total sales value at the bottom for total sales. I want to create a
report that displays all three values in it. How can I do this.

 

[Guest]

Throught the use of Union queries and the IIF function I created one query giving me the results for all tranactions. The fields created are order number, invoice date, quantity, price, ext, type of tranaction, category of product sold, and salesman. All I need is a report grouped by salesman giving the total dollar value for the various types of sales. I don't need all the detail associated with it.

 

[Allen Browne]

So after creating the UNION query, you can use that as a source to another
GROUP BY query to get just the totals?

Alternatively, you can create a report with a Group Footer to give you the
totals (in the Sorting And Grouping dialog) and then set the Visible
property of the Detail section to No so you do not get all the detail lines.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Throught the use of Union queries and the IIF function I created one query

giving me the results for all tranactions. The fields created are order
number, invoice date, quantity, price, ext, type of tranaction, category of
product sold, and salesman. All I need is a report grouped by salesman
giving the total dollar value for the various types of sales. I don't need
all the detail associated with it.

 

[Guest]

How can I just ge tthe totals for each type of tranaction using a query.

 

[Allen Browne]

For that you would either need to use a subquery (very inefficient), or a
stacked query.

For a stacked query, create another query that has the UNION query as its
source table. Depress the Totals button on the Toolbar. Access adds a Total
row to the grid. You can now GROUP BY the transaction type, and SUM the
amounts.