Duplicate Records in Query, Tried Everything

[burrina]

I keep getting duplicate records in my query result. I tried using Unique Records and also Unique Values and that did not work either.


Here is the SQL for it. I have used 2 queries combined to try and get my result. InvoiceFirstPart shows me Customers Order Details.
InvoiceSecondPart shows me the Products Ordered and Payments made.

[SELECT DISTINCT InvoiceFirstPart.OrderID, InvoiceFirstPart.CustomerID, InvoiceFirstPart.EmployeeID, InvoiceFirstPart.OrderDate, InvoiceFirstPart.PurchaseOrderNumber, InvoiceFirstPart.ShipDate, InvoiceFirstPart.ShippingMethodID, InvoiceFirstPart.Comment, InvoiceSecondPart.ProductID, InvoiceSecondPart.Quantity, InvoiceSecondPart.UnitPrice, InvoiceSecondPart.Discount, InvoiceSecondPart.[Extended Price], InvoiceSecondPart.Payment, InvoiceSecondPart.PaymentMethod, InvoiceSecondPart.CheckNo, InvoiceSecondPart.DatePaid, InvoiceFirstPart.CustBillingAddress, InvoiceFirstPart.CustCity, InvoiceFirstPart.CustStateOrProvince, InvoiceFirstPart.CustZIPCode, InvoiceFirstPart.CustShipAddress, InvoiceFirstPart.CustShipCity, InvoiceFirstPart.CustShipStateOrProvince, InvoiceFirstPart.CustShipZIPCode, InvoiceFirstPart.CustContact, InvoiceFirstPart.CustShipVia, InvoiceFirstPart.CustTerms
FROM InvoiceFirstPart RIGHT JOIN InvoiceSecondPart ON InvoiceFirstPart.OrderID = InvoiceSecondPart.PayID
ORDER BY InvoiceFirstPart.OrderDate;]


Thanks for taking a look.

[orange]

Please post

1)a jpg of your tables and relationships and
2) tell us exactly what you are trying to do.

[burrina]

Trying to Create a Invoice from the 2 queries.

[orange]

Your data model needs work.
You would normally have an tblOrderDetails and a relationship
between Orders(1) and OrderItems(Many).
Also, customers Order Product, so a ProductId would be an OrderItem.
So there would be a 1 to Many relationship between Product (1) and OrderItems (Many).

Here is an overview of the Order/purchase/Invoice cycle
http://en.wikipedia.org/wiki/Order_to_cash

Existing data models that may be of some value to you:
http://www.databaseanswers.org/data_...ders/index.htm
http://www.databaseanswers.org/data_...ices/index.htm

Here are some documents and videos related to Database and Access. I hope you find them useful.

Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:

These are quite good for learning by Watching/Listening rather than reading.

Logic Data Modeling 1 - Introduction - YouTube Logical data modeling

Logic Data Modeling 2 - Candidate Key - YouTube Candidate key

Logic Data Modeling 3 - Normalization - YouTube Normalization

Logic Data Modeling 4 - Normalization Example - YouTube Normalization example

Logic Data Modeling 5 - 1st Normal Form - YouTube 1st Normal form

Logic Data Modeling 6 - 2nd Normal Form - YouTube 2nd Normal form

Logic Data Modeling 7 - 3rd Normal Form - YouTube 3rd Normal form

Logic Data Modeling 8 - Entity Relationship Diagram, part 1 - YouTube E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP