Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Report Grouping (2003)

    I've created a report from a table, which should give me quantities and part numbers. I've grouped the part numbers, and put all the details in the group header. I know that 1 part has 3 quantities (18,20,21) totalling 53, but when I run the report, only the 15 is showing. If I ungroup the report, I get the 3 lines showing all the quantities. Where have I gone wrong?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Report Grouping (2003)

    What is the 15? You did not mention 15 until you said it is showing.

    Is the quantity in the header or the Detail section. If you want to see multile quantities, they need to be in the Detail section.
    Regards
    John



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

    Re: Report Grouping (2003)

    Also, 18, 20 and 21 don't add up to 53.

    If you want to sum the quantities in the group header, you have to use a text box with a Control Source that looks like this:

    =Sum([Quantity])

    where Quantity is the name of the field. The name of this text box should be different from that of the field - if it's named Quantity (in this example), Access gets confused.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    Sorry, typing error. Should be 18,20,15. I just want to show 53

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    Sorted. Adding Sum([Quantity]) did it.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    Nort sorted. I'm getting the right quantites now, but can't get the total sum for the prices in the report. In the Part No Header, I have the Sum of the quantities as you suggested (Quantities2), which works fine. Also in the header, I have a control to work out the cost [Price Each]*[Quantities2], which also gives me the right figure. In the report footer, I have Sum[Cost], which is giving me a totally wrong figure.

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

    Re: Report Grouping (2003)

    Try setting the control source of the total sum text box to

    =Sum([Price Each]*[Quantities2])

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    I then get asked to enter Parameter [Quantities2], even though it's there in the header. Is that where I'm going wrong. I want to group the quantities for each part number, but display the results in tabular form, so I put all the controls in the header, apart from the grand totals which are on the report footer.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    This is what I'm trying to achieve, but the figures are wrong. I've obviously removed and changed some things.
    Attached Files Attached Files

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Report Grouping (2003)

    You cannot sum calculated controls. Both Quantity2 and Price are calculated. You need to move the calculation to a query to be able to sum them.
    Your table does actually have a Total Price field. Maybe you can use that, but I don't understand your data I'm afraid.
    Regards
    John



  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    The problem with that, is that the table is made from a query. A form asked for a start invoice and an end invoice, which then runs the query to create the table. At the same time another form opens with the selection, say from 1 to 50. From this selection, we may only want to print numbers 1 to 10, and 35 to 40. These numbers are highlighted on the selection form, and when the Ok button is pressed, the report is created from the table, filtering just the numbers we require. I don't know another way to filter the selection in a query.

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

    Re: Report Grouping (2003)

    You're grouping on reference, but reference is not a field in the table, so what is its purpose?
    It makes no sense to include the Price Each field in the group header, since its value varies from record to record. In the group header it'll display the value of the first record in the group.

    The control source of the Total Price text box should be

    =Sum([quantity]*[Price Each])

    or perhaps

    =Sum([Total Price])

    but in the latter situation, the name of the text box should be changed to avoid a circular reference.

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    I deleted reference in error, I should have just deleted the data. These are the part numbers, which I am grouping on, so the fact that the Price each field gives the first record of the group should be ok, as line entries with the same part number (reference), will have the same Price Each.

  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Grouping (2003)

    I have a form to filter the results, which initially opens a report. This is part of the code in the OnClick properties of the button which opens the report :-

    For Each varItem In Me.lstInvoices.ItemsSelected
    strFilter = strFilter & "," & Chr(34) & Me.lstInvoices.ItemData(varItem) & Chr(34)
    Next varItem
    strFilter = "code In(" & Mid(strFilter, 2) & ")"

    I tried opening a query instead of a report, by adding this:

    DoCmd.OpenQuery stDocName, acViewPreview, strFilter

    but it doesn't work. Is there a way to run the query with the filter? I have made reference to the stDocName.

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

    Re: Report Grouping (2003)

    OpenQuery doesn't let you specify a where-condition (look up OpenQuery in the Access VBA help). But that doesn't matter, since you shouldn't let end users open a query anyway. They should only use forms and reports.
    You can change the SQL of a query, but that is fairly complicated.

Page 1 of 2 12 LastLast

Posting Permissions

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