Generating an Invoice Report - Hopefully a simple question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used the tables wizard to set up 3 tables - one for customer
information, one for products and one for the sales person who sold the
product.

I used the reports wizard to create an invoice showing the order by the
customer, the products they purchased, and the cost. The problem is that one
invoice is showing multiple customers. I would like the invoice to show one
customer per invoice. How do i do this??

Thanks for any help anyone can provide.

Scott
 
Your tables are not correct. You need:
1. table for products
2. table of salespersons
3. table for customer information
4. Invoice table showing date of sale, SalesPersonID and CustomerID
5. Invoice line items table showing ProductID, Quantity and Price

You need a report/subreport. The report should be based on a query that
includes the Invoice table, salesperson table and the customer table. The
subreport should be based on a query that includes the Invoice items table
and product table.
 
As well as the 3 tables you describe, you need:
- An Invoice table (one customer can have multiple invoices over time);
- An InvoiceDetail table (one invoice can have multiple line items).

If that's a new idea, open the Northwind sample database, and choose
Relationships on the Tools menu. Notice how they relate customers, products,
orders, and order details.

You will then create a query containing the Customers, Invoice and
InvoiceDetail tables, and use this as the source for your report.

In report design view, open the Sorting And Grouping dialog (View menu).

Choose the InvoiceID field. In the lower pane of the dialog, specify a Group
Header and a Group Footer. Access adds 2 bars across your report.

Put the header information (invoice number, date, and the address panel)
into the InvoiceID Group Header section.

List the line items in the Detail section.

Put the invoice total into the InvoiceID Group Footer Section.

Again, Northwind has a sample Invoices query and Invoice report as an
example.
 
Back
Top