Repeating records and a few other items..

[stillwillyboy]

I have my InvoiceNumberList table with the fields:

InvoiceNumber
CustomerID
InvoiceDate
PurchaseOrderNumber
I included the PO field because each invoice will have only one PO, even if it has more than one line of detail and/or more than one part listed. I’m wondering if the PO needs to be included.

I have the Orders table with the fields:
OrderID
PartID
CustomerID
BranchID
InvoiceNumber

I have the OrdersDetail table with the fields:
OrderID
OrderDate
ShippingTicketNumber
PurchaseOrderNumber
QuantitySold
ShippingCharges
Comments

I created a query (qry-OrdersQuery) that pulls the info from both the Orders and OrdersDetail tables. It works as I want it to.

I created another query (ar—invoicetotal) that is based upon the above query. I am wanting to use the new query to give me my invoice totals for the invoices that I print (with the info from the OrdersDetail table) and also for my AR The tables in this new query are: InvoiceNumberList, qry-OrdersQuery and Customers.

When I run the ar—invoicetotal query, I get the same info three times. Such as:

CustID Invoice# CustName PO GrandTotal
12 1 Eagle 10 10.00
12 2 Eagle 11 261.90
12 3 Eagle 12 200.00
12 1 Eagle 10 10.00
12 2 Eagle 11 261.90
12 3 Eagle 12 200.00
12 1 Eagle 10 10.00
12 2 Eagle 11 261.90
12 3 Eagle 12 200.00

Why?

Plus, I have the code now to increment the invoice numbers, but how do I implement it so it can do its job? In other words, how do I make it execute when I process the Order info into invoices?

Keeping in mind that one invoice can only be for one PO, but still have multiple lines of detail and more than one part listed, does it look like I am headed in the right direction?

TIA,

Bill

 

[jrbarnett]

Bill

Normally, rows repeat in query results when tables or queries are not joined together properly. What you have to look for is either a missing link on the InvoiceNumber or PO columns, because the data there repeats itself.

John

 

[ccoindre]

You might try adding DISTINCT or DISTINCTROW to the SELECT portion of the SQL view of the query. See VBA help for example.