Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Additional Detail Section (A2k)

    Hello all

    I need to add another detail section with the attached report.
    In the report I have added teh fields [Description] & [Reason For RTeturn] which needs it's own detail section.
    At the moment,if more than 1 item is in the credit table, only one shows in the report.
    I think I have to tweak the Sorting & Grouping ?
    Can anyone help ?

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

    Re: Additional Detail Section (A2k)

    Hi Dave,

    Since you only included the report in the database, I have no idea about the record source. Is qryPARTSAUDIT a Totals query?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    Hans, does this help ?
    I have limited info now at home

    SELECT [PARTS AUDIT].[EST No], [PARTS AUDIT].SUPP, [PARTS AUDIT].[INVOICE DATE], [PARTS AUDIT].SUPPLIER, [PARTS AUDIT].[INVOICE No], [PARTS AUDIT].[TOTAL RETAIL], [PARTS AUDIT].[TOTAL NETT], [PARTS AUDIT].PROFIT, [PARTS AUDIT].DISCOUNT, [PARTS AUDIT].[DATE COMPLETED], [PARTS AUDIT].[STOCK ITEMS], [PARTS AUDIT].[CREDIT RETAIL], Sum(IIf([PARTS CREDITS]![CREDIT AMOUNT],[PARTS CREDITS]![CREDIT AMOUNT],0)) AS PCredits, Sum(IIf([PARTS CREDITS]!CREDITNETT,[PARTS CREDITS]!CREDITNETT,0)) AS PNETT, [PARTS CREDITS].DESCRIPTION, [PARTS CREDITS].[REASON FOR RETURN]
    FROM [PARTS CREDITS] RIGHT JOIN [PARTS AUDIT] ON ([PARTS CREDITS].[EST No] = [PARTS AUDIT].[EST No]) AND ([PARTS CREDITS].SUPP = [PARTS AUDIT].SUPP)
    GROUP BY [PARTS AUDIT].[EST No], [PARTS AUDIT].SUPP, [PARTS AUDIT].[INVOICE DATE], [PARTS AUDIT].SUPPLIER, [PARTS AUDIT].[INVOICE No], [PARTS AUDIT].[TOTAL RETAIL], [PARTS AUDIT].[TOTAL NETT], [PARTS AUDIT].PROFIT, [PARTS AUDIT].DISCOUNT, [PARTS AUDIT].[DATE COMPLETED], [PARTS AUDIT].[STOCK ITEMS], [PARTS AUDIT].[CREDIT RETAIL], [PARTS CREDITS].DESCRIPTION, [PARTS CREDITS].[REASON FOR RETURN]
    HAVING ((([PARTS AUDIT].[EST No])=[forms]![frmParts]![EstimateNo]) AND (([PARTS AUDIT].SUPP)=[forms]![frmParts]![supp]));


    p.s

    Sorry for the incorrect Access tables rules (ie, spaces in field names) but this is a very old database.

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

    Re: Additional Detail Section (A2k)

    You should move the Description and Reason for Return fields to the Detail section. They are in the report footer now, that's not what you want.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    Hans

    See attached PNG.
    In the detail section, the data is duplicated several times.

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

    Re: Additional Detail Section (A2k)

    You could add Description to the Sorting and Grouping window. Move it up to just above Reason for return.
    Set the Group Header property (in the Sorting and Grouping window) for Description and for Reason for Return to Yes.
    Move the text boxes for these fields to their respective group headers.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    Very close Hans, but still duplicating ?

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

    Re: Additional Detail Section (A2k)

    Add another grouping level for PARTS AUDIT.SUPPLIER.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    Sorry Hans
    Still struggling here.
    I've attached a stripped down version if you can help.
    I would like to separate the sales from the returns.
    Preferably the sales should be above the returns so I can see what items have been returned.

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

    Re: Additional Detail Section (A2k)

    Can you explain in detail what exactly you want? For example, you write that sales should be above returns. But on what should they be grouped?

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    As below.
    The sales are in a section of their own and grouped.
    A separate section showing items returned and the reasons why.
    It's easier to explain with a PNG.
    Thanks again.

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

    Re: Additional Detail Section (A2k)

    You need a subreport for that. Does the attached version do what you want?

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    Hans

    Perfect as normal <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Additional Detail Section (A2k)

    Hans

    A bit of a glitch !!
    If there are more than 1 items returned, some of the sale items are duplicated.
    Would you mind checking it out.
    Thanks again

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Additional Detail Section (A2k)

    Can you use SELECT DISTINCT ........ ?

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
  •