I am trying to write a query that will give me all of the entries from one table and then add the lines from another table that have the same part number. The problem is that I cannot get the first table to show all line items even though I have a join that states to do this. Also, I know this is a problem but the table that I want to display all the line items will have duplicate part numbers. This is the way it should be. The parts table lists all the parts that should ship and the second table shows the parts that did ship. That is why there could be duplicate part numbers because possibly 4 of one item needs to ship and then I want to record the serial numbers of each item that did ship. If you could look at my sample database, you will find a part and order table and the query I have attempted is named report.
Any help is very much appreciated!
P.S. I had this posted earlier and someone wrote a query (query1) but it returned duplicate line items. Could someone take a look at what I have. Please ask any questions!!
First of all, the query is wrong. Your missing a character for the inline view. The SQL for Query1 should be:
FROM PARTS AS P LEFT JOIN [select * from ORDERS O where orderID = 43]. AS V ON P.partID=V.partID
ORDER BY P.partID, O.serialNum;
Second, in your parts table, you have four records listing the same part and quantity (partID 90319022). Of COURSE it's going to give you duplicate records. Instead of four records in PARTS for this part showing a quantity of 1, you should have ONE record for this part showing a quantity of 4.