Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2021
    Posts
    14

    1 line per Invoice in Report

    Hi!! I Need some help extracting data from a table for a report.



    My table structure is as follows:

    Cust-Code Inv-No Inv-Date Details Rent-pm Months Amount
    282 133 01.03.22 Rent 150.00 5 750.00
    282 133 01.03.22 VAT @ 5% 37.50
    357 134 04.03.22 Rent 500.00 4 2000.00
    357 134 04.03.22 VAT @ 5% 100.00
    405 135 06.03.22 Rent 200.00 6 1200.00
    405 135 06.03.22 VAT @ 0% 0.00

    There will always be 2 entries per Cust-Code in the table per Invoice.
    1 for RENT and the other for VAT (whether 0% or 5%).
    Some Customers are billed VAT @ 5%
    Some Customers are billed VAT @ 0%


    I need a report as below only for Invoices having VAT @ 5%
    Invoices having VAT @ 0% should be ignored
    Report should have only 1 line per Invoice as below

    Cust-Code, Invoice-Num, Rent-Amt, VAT-Amt, Total-Amt(Rent+VAT)
    282 133 750.00 37.50 787.50
    357 134 2000.00 100.00 2100.00

    Inv# 135 has been omitted from the above report as VAT is at 0%.

    Any help and pointers would be greatly appreciated.
    I'm using Access 2003.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Criteria for literal string would be

    "Details <> 'VAT @ 0%'"

    Note the red enclosed single quotes.

  3. #3
    Join Date
    Jul 2021
    Posts
    14
    Quote Originally Posted by davegri View Post
    Criteria for literal string would be

    "Details <> 'VAT @ 0%'"

    Note the red enclosed single quotes.

    Thx!!

    But how do I get the data in only 1 line?
    RENT is appearing as 1 record in the table and
    and VAT as another record in the table.
    I'd like a table as below which I can use as a recordset source for my report.

    Cust-Code, Invoice-Num, Rent-Amt, VAT-Amt, Total-Amt(Rent+VAT)
    282 133 750.00 37.50 787.50
    357 134 2000.00 100.00 2100.00


    Sorry for the naïve question, but I'm revisiting Access after 18 years and am totally blank.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You make the query a totals query in which you group by customer code and invoice number and sum the amount.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    I would have thought vat applied to the transaction and so would be in the same record as rent?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-10-2019, 08:20 PM
  2. Replies: 3
    Last Post: 10-19-2015, 11:05 PM
  3. Replies: 2
    Last Post: 09-14-2015, 09:01 AM
  4. Replies: 2
    Last Post: 03-23-2015, 08:32 AM
  5. Add New Line Item Detail to Invoice Based on Status
    By breakingme10 in forum Programming
    Replies: 2
    Last Post: 06-23-2014, 04:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums