Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Assistance (03)

    I am looking to create a report from a query based on a secondary account assignment. The query structure looks like this:
    <table border=1><td>Accountt</td><td>Description</td><td>SecondaryAccount</td><td>Amount</td><td>1234</td><td>Descr_01</td><td>1000</td><td>100.00</td>
    <td>2345</td><td>Descr_02</td><td>1000</td><td>150.00</td>
    <td>9876</td><td>Descr_03</td><td>1200</td><td>200.00</td>
    <td>8765</td><td>Descr_04</td><td>1300</td><td>300.00</td></table>


    The displayed report should look something like:
    <table border=1><td>SecondaryAccount</td><td>Total Amount</td><td>1000</td><td>250.00</td>
    <td>1200</td><td>200.00</td>
    <td>1300</td><td>300.00</td></table>
    My challenge is determining how to summarize the totals based on the secondary account. I believe a footer section should contain the expression to summarize by the secondary account.

    One of the expressions I have in mind: =Sum([Amount)] Where SecondaryAccount = 1000

    Thanks,
    John

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

    Re: Report Assistance (03)

    One option is to create a Totals query based on the query you already have. Add the SecondaryAccount and Amount fields, with the Total option set to Group By for SecondaryAccount and to Sum for Amount. Use the new query as record source for the report.

    The other option is to group the report by SecondaryAccount, and to hide the Detail section (or set its Height to 0).
    Put a text box in the group footer (or header) with Control Source

    =Sum([Amount])

    Access automatically sums over the group only, so there is no need to use a where-condition.
    (You can build this report using the Report Wizard, grouping and totaling are options there).

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    Thank you for the suggestion. I'll give it a try.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Too many long hours and not enough sleep.

    Regards,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    Your suggestion did work but is not quite what I am after. I would like to be able to select/report upon certain "secondary accounts".

    Regards,
    John

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

    Re: Report Assistance (03)

    You can create a form with a multi-select list box in which you can select secondary accounts, and a command button that passes a where-condition to the report.

    See <post:=480,301>post 480,301</post:> for an example of how to do this.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    What a great example of using multi-select in a list box. Still quite not what I am after. I would like to free-form the report placing certain secondary accounts with totals in the report (using a columar format). I'm just wondering if Access is the right place to do the reporting. I would hope so as the query data resides in the database. Perhaps using Excel is an alternative method to generate the report.

    <table border=1><td>SecondaryAccount</td><td>Total Amount</td><td>1000</td><td>250.00</td><td>1200</td><td>200.00</td><td>1300</td><td>300.00</td><td>1500 Total</td><td>750.00</td></table>

    <table border=1><td>1050</td><td>10.00</td><td>1250</td><td>30.00</td><td>1350</td><td>60.00</td><td>1550 Total </td><td>100.00 </td></table>

    Regards,
    John

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

    Re: Report Assistance (03)

    I'm afraid I don't understand what you want.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    Let me try explaning it another way.

    The fields in the query consist of accounts with associated values which can be used to generate a typical profit & loss statement. What I am after is replicating the P&L within an Access report; maintaing flexibility as to how I want to display the accounts in the report. Notice that the accounts may not be in sequential order.

    Hope this helps clear things up.

    Regards,
    John
    Attached Images Attached Images

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

    Re: Report Assistance (03)

    You can do this by adding a number field in which you specify the grouping. Assign the value 1 to the accounts that should be listed first, assign 2 to the accounts that should be listed next, etc.
    You can then group the report on this extra field, with a subtotal in the group footer.
    If you need multiple grouping levels, add extra fields to match the levels.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    I was able to utilze your suggestion and can produce a report with appropriate sub-totals. One thing I can not get is the account description for the sub-totaled account in the footer. In this case account 4000 should have a description of "4000 Descr".

    As an example my query contains:


    <table border=1><td>Account</td><td>tblAcctDescr</td><td>Amount</td><td>SubtotalOn</td>
    <td>1234</td><td>1234 Descr</td><td>100.00</td><td>4000</td><tr><td>2345</td><td>2345 Descr</td><td>200.00</td><td>4000</td><tr><td>3456</td><td>3456 Descr</td><td>300.00</td><td>4000</td></table>

    I have a table named "tblAcctDescr" which includes all account descriptions including the 4000.

    The detal section includes:
    [AcctDescr} [Account] [Amount]

    The footer section includes:
    [AcctDescr} [SubtotalOn] Sum([Amount])

    In this example the footer description of [SubtotalOn] displays as the first item in the detail section or 1234 Descr

    Thanks,
    John

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

    Re: Report Assistance (03)

    If your query returns 4000 in the SubtotalOn field for a group of records, the SubtotalOn field should also display 4000 in the group footer. Are you sure the text box in the group footer has the correct field as Control Source?

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    I have reviewed the text box in the footer and have "=tblAcctDescr!Descr" as the source control. If I execute the report, I am prompted to enter a parameter value.

    I would agree with you that it should work.

    Regards,
    John

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

    Re: Report Assistance (03)

    If your query has a field SubtotalOn, you should use that as control source, or

    =[SubtotalOn] & " Descr"

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Assistance (03)

    Hans,

    I'm still having a challenge. I'm not getting the description of the "SubtotalOn" in the footer. As you can see I currently have the text box set to "AcctDescr" which is returning the first item description of the detail. If I change it to "SubtotalOn" I get the numeric value not the description of "SubtotalOn".

    Regards,
    John
    Attached Images Attached Images

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

    Re: Report Assistance (03)

    Try
    <code>
    =DLookup("Descr","tblAcctDescr","Account=" & [SubtotalOn])
    </code>
    Substitute the correct name for the Account field in tblAcctDescr if necessary. If that doesn't do what you want either, I cannot solve your problem without seeing (a stripped down copy of) your database. See <post#=401925>post 401925</post#> for instructions.

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
  •