Getting duplicate information in my query

[Toollady]

I have been trying for weeks to get this query to work. Each time I get duplicating info. I have attached both the query and the results

[June7]

You are not linking on key fields. Invoice record should have the JobID, not PropertyID.

[John_G]

First question is - what is the information you are trying to show in the query? From what you show in the diagram it is not clear.

Your tblInvoiceLineItems is not structured correctly - each invoice has many line items, and that table does not have a field for the line number. Add the line number field, and make it part of the key.

Your query I think is missing a table - tblInvoice, if that is what you want to show. The relationship would be between NewOrders and tblInvoice, using InvoiceNumber, and not PropertyNumber as June7 pointed out.

HTH

John

[June7]

Actually, PK in InvoiceLineItems is not critical, nor is a line number for each item of the invoice (can be generated on report), but neither could hurt.

Can get by without the Invoices table but means having to repeat invoice info such as JobID and InvoiceDate in every line item record. If you do have an Invoices table then it should have JobNumber. Include all 3 tables in the query. Regardless, PropertyID does not belong in LineItems.

Is there a Customers table? Should CustomerID be in NewJobOrders?