Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Sum in report access 07

    I'm looking for the correct path to begin the vba to fix a problem I have in a report.
    This is an account summary report with a Union Query record source.

    The main header of the report is the 1.) Account Number, then 2.) the invoice - (1-to-many/1 Account can have many invoices) and from that invoice there can be 3.) a credit memo; 4.) additional internal cost; 5.) an invoice revision; 6.) and invoice notes/total 6 headers (1-to-many/1 invoice can have many credits, notes, costs and revisions)
    In the detail section it will show total paid toward the invoice and it's balance (if any). Headers 3.credits; 4.internal cost; 5.revisions; and 6.notes can have multiple records.
    There is an Invoice footer that totals credits and internal cost with =Sum[].
    The Account number footer shows me the total invoices for the account using =Sum[invamt]

    The problem I have that if I have more than one in the sections 3,4,5 or 6, it duplicates each section the max number of times.
    i.e.
    INV123456 7/2/12 $109
    CM82 7/3/12 -$1 Freight charge error
    7/3/12 Spoke to Kim about the incorrect freight bill w/c/b
    $100 $9
    CM82 7/3/12 -$1 Freight charge error
    7/5/12 Kim called back, they processed this for $100
    $100 $9


    The query does repeat the invoice when there is more than one of the many side records which is what the query should do. Because this is showing twice, the $109 invoice amount will be in the footer sum twice.

    Did I simply build this wrong?

    I tried different header and footer options, but found the same repeating results.

    I heard there is vba to fix it - although I'm not able to find clean examples.
    Can I vba this report to show me each invoice once and each of the many side entries once?
    How this example should show and sum on my report is:
    INV123455 7/1/12 $100
    INV123456 7/2/12 $109
    CM82 7/3/12 -$1 Freight charge error
    7/3/12 Spoke to Kim about the incorrect freight bill w/c/b/
    7/5/12 Kim called back, they processed this for $100
    $100 $9
    ACCT TTL $209 **This is currently summing at $318**

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    What happens when you combine items 2,3,4, and 5 in the same section (Detail Section)? Try that, and move the notes (item 6) to the report footer.

    It'a also possible that your union query is off. Normally, there should be no reason to use a union query for a simple calculation like an invioce total, as all transactions are normally in the same tblTransaction table. Just that there are different XTypes (transaction types). XType 1 might be debit; 2 might be credit. Then there might be another field Desc with a description of the debit (Tax) or credit (Refund). Etc...

    Sam

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    same thing - everything repeats......

    I have multiple tables for each

    I need to go back to the drawing board maybe

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Instead of a union, try reporting on only one table, since the transactions are duplicated several times. If one table doesn't have all the xactions, try creating a temp table with the records from one table, and appending to it from the other tables only those xactions that aren't already in the new table. Run the report using the new temp table as the recordsource.

    Sam

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    the union is to pull in different sales levels - I need to show invoices for salesman weather he is the 1st, 2nd or 3rd salesman listed on the invoice

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In that case, you have an entirely different problem: your data is not normalized. I can't explain it too well (it's after 10 PM, and I'm tired); I suggest you look it up in the help file.

    In any case, the way to solve it (that's always more important to know than defining the problem, isn't it [tongue firmly in cheek!]?) is to have the financial information in only one table, such as a sales table, along with the order number (for example), total cost, date shipped, date paid, etc. You can keep all side data, such as the personnel involved, in a different table with a one-to-many relationship.

    While we're on the subject, the order table should be separate as well, because there may be multiple shipments per order.

    A robust Access sales database would have multiple tables: vendors, customers, orders, shipments, item master, parts lists (if applicable), etc. in order to keep the data in the tables from stepping on each others' feet. Each table would have a one-to-many relationship with the other tables. Or at least use primary and foreign keys so you don't have the same text a hundred times in the same file.

    If you do this, you won't need the union.

    Sam

Posting Permissions

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