Combining Invoices to Vendor on one Report

FaxMeBeer

New Member
Joined
Apr 20, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I am sure that this has been asked, but I cannot come up with the right search terms to find it, I'm sorry.

I have a simple database with two tables (a "vendor" table, and an "invoice" table). Any vendor can have one, or any number of invoices. I want to make a report showing all invoices related to a vendor, rather than a report that has the vendor + one invoice, vendor + second invoice...etc..

I really just want something like a standard statement to be the end result, so that you'd see something like:

-------------------------------------------------------------------------
| Vendor Name |
| Address |
| City, State Zip |
| |
| |
| Invoice # 123456, $1,000.00, Due: 08/31/2020 |
| Invoice # 456123, $1,500.00, Due: 08/25/2020 |
| |
| |
| |
| |
| |
| |
______________________________________________________________________|

All I can manage to produce is a single the an individual page for each invoice, and that isn't what I'm trying for. Thanks for your help!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,396
Office Version
  1. 2016
Platform
  1. Windows
Hi FaxMeBeer,

If your Invoices look like this:

Book2
ABCD
1VendorInvoice Value Due Date
2ACME123456 $ 1,000.00 31-Aug-20
3ACME456123 $ 1,500.00 25-Aug-20
4Pomer919191 $ 2,500.00 02-Sep-20
5ACME727272 $ 7,500.00 02-Nov-20
6Pomer123123 $ 1,230.00 10-Oct-20
Invoices


Then this can produce the Statement for the stated Vendor:

Cell Formulas
RangeFormula
A6:C11A6=IF(COUNTIF(Invoices!$A$2:$A$9999,$A$1)<ROWS($A$5:$A5),"",INDEX(Invoices!B$2:B$9999,AGGREGATE(15,6,ROW(Invoices!$A$2:$A$9999)/(Invoices!$A$2:$A$9999=$A$1),ROWS($A$5:$A5))))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,708
Office Version
  1. 365
Platform
  1. Windows
Toadstool,
This appears to be an Access question, not an Excel one (posted in the Access Question Forum and mentions databases, tables, and reports).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,708
Office Version
  1. 365
Platform
  1. Windows
FaxMeBeer,

Try creating a query between your two tables, put criteria on the vendor field, and use this query as the Record Source for the Report.
If you make it a "Parameter Query", when you go to open the Report, it will prompt you to enter the vendor, and then the Report will just show that vendor.
See here for how to create a Parameter Query: Access 2010: Queries: How to Create a Parameter Query
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
452
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
A feature I've not played with much, but I think you can group the records and set it to start a new page with each group.

 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,708
Office Version
  1. 365
Platform
  1. Windows
A feature I've not played with much, but I think you can group the records and set it to start a new page with each group.
Yes, if the goal is to print all of them out (and not just one particular one), that method should work
Basically, you just put the vendor information in the Group Header, the Invoice information in the Detail section, and any Totals you want for the vendor in the Group Footer section.
And you can put the Page Break in the Group Footer section at the bottom.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,921
Messages
5,627,631
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top