Report/Subreport/Recordset Question (Access2K/Win2K)

Topic

New Reply

I have a monthly sales report, with a subreport, much like the Orders form in Northwind.

The report recordsource is a query, and the subreport’s recordsource is another query with OrderID linking the two recordsets in the report.

This gives me one line in the report across the page for each order, and if there is more than one product on an order those products and quantities are listed one above the other. Then the next sales order line comes across the page underneath.

I am happy with this presentation, it looks neat and tidy whilst being easy to read.

My problem is that i want to have some summary data in the report fotter, eg. total number of items sold listed by item. Now that would be easy if the data were in the report, but the item data and quantitiy are in the subreport.

So, my question is how is the best way of doing this??

Can i reference the subreport controls and use the SUM function?? If so, i can’t find any data or examples on syntax for referencing subreport controls.

Should i run to make table queries?? Then i could SUM from the second data table. (Seems a long way round the problem).

I’ve tried the recordsource as one query, but where there is more than one product on any given order, the other fields repeat themselves relecting the data in the table.

Reply | Quote

Replies

Put a text box in the report footer of the subreport to sum the number of items. This text box can be hidden (Visible = No) if you like.
Put a text box in the main report, in the same section as the subreport. Set its control source property to something like

=[sbrProducts]![txtItemsSold]

with the appropriate names substituted. The name of the subreport must be the name it has as a control on the main report.
Set the Running Sum property of the text box to Over All. Name it (for example) txtRunningSum. This text box can be hidden too.
Put a text box in the report footer of the main report. Set its control source property to

=[txtRunningSum]

Reply | Quote

Cool! I got that working, thank you. I have now learned how to link the report and subreports!

Can i modify this to be able to list the various different products sold with their respective totals in the main form footer?? I.E. If i have sold 5 different products in the footer will be those 5 different products with their respective totals..

Reply | Quote

Hi Francois

“If you want a list of all items with number that have been sold for each one, create a new subreport based on a new total query end put it in the report footer.”

I tried this but could not get it working. I built a simple totals query on the products table, grouping by product and SUM of the Quantity, but this obviously give me totals for the whole table.

I could not work out how to link this query/subform to the rest of the report…

Reply | Quote

You should add the same conditions as in the main report.
Probabily you’ll have to add the main order table to get the month and year of the order in the query and doing a filtering as in the main report.
Doing so, you shouldn’t have to link the subreport to the main report.

This is the sql of the query for the totals for May 1998 in the Nothwind DB
SELECT [Order Details].ProductID, Sum([Order Details].Quantity) AS SumOfQuantity
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Month([OrderDate]))=5) AND ((Year([orderdate]))=1998))
GROUP BY [Order Details].ProductID;

Reply | Quote

HansV‘s solution will give you one total of all items sold.
If you want a list of all items with number that have been sold for each one, create a new subreport based on a new total query end put it in the report footer.

Reply | Quote