Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report/Subreport/Recordset Question (Access2K/Win2K)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report/Subreport/Recordset Question (Access2K/Win2K)

    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

    <code>=[sbrProducts]![txtItemsSold]</code>

    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

    <code>=[txtRunningSum]</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report/Subreport/Recordset Question (Access2K/Win2K)

    <!profile=HansV>HansV<!/profile>'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.
    Francois

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report/Subreport/Recordset Question (Access2K/Win2K)

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

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report/Subreport/Recordset Question (Access2K/Win2K)

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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report/Subreport/Recordset Question (Access2K/Win2K)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •