If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
duplicate results in query
Hi, I am getting duplicate results when I run a query. Here are the
details. Bothe tables have the same data only dates have changed. what I am trying to do is find out which dates have changed. The problem is this. Some purhcase orders are split so the same PO and line has 2 dates. when i combine the 2 tables I get 4 lines for these instead of 2. example Vendor PO Line Qty Date X 1234 1 2 7/1/2010 X 1234 1 2 8/1/2010 When i combine this with the other table which is the same for these I get the following Vendor PO Line Qty Date1 Date2 X 1234 1 2 7/1/2010 7/1/2010 X 1234 1 2 7/1/2010 8/1/2010 X 1234 1 2 8/1/2010 7/1/2010 X 1234 1 2 8/1/2010 8/1/2010 The other problem is any dates in table 2 that are different do not even show because i am doing an inner join which is obvioulsy wrong. If I do a left join I get way to many results. Any ideas. Here is the current sql. SELECT tblPOBM_baseline.Vendor, tblPOBM_baseline.[Vendor name], tblPOBM_baseline.[Purch Doc], tblPOBM_baseline.Item, tblPOBM_baseline.Rel, tblPOBM_baseline.OTyp, tblPOBM_baseline.MRPC, tblPOBM_baseline.Material, tblPOBM_baseline.[Short Text], tblPOBM_baseline.PromisedQt, tblPOBM_baseline.[PD release], tblPOBM_baseline.[Del Date], tblPOBM_baseline.Exc, tblPOBM_baseline.CC, tblPOBM_baseline.Price, tblPOBM_baseline.Value, tblPOBM_current.PromisedDa AS [New Date], tblPOBM_baseline.PromisedDa AS [Old Date] FROM tblPOBM_baseline INNER JOIN tblPOBM_current ON (tblPOBM_baseline. [Purch Doc] = tblPOBM_current.[Purch Doc]) AND (tblPOBM_baseline.Item = tblPOBM_current.Item) GROUP BY tblPOBM_baseline.Vendor, tblPOBM_baseline.[Vendor name], tblPOBM_baseline.[Purch Doc], tblPOBM_baseline.Item, tblPOBM_baseline.Rel, tblPOBM_baseline.OTyp, tblPOBM_baseline.MRPC, tblPOBM_baseline.Material, tblPOBM_baseline.[Short Text], tblPOBM_baseline.PromisedQt, tblPOBM_baseline.[PD release], tblPOBM_baseline.[Del Date], tblPOBM_baseline.Exc, tblPOBM_baseline.CC, tblPOBM_baseline.Price, tblPOBM_baseline.Value, tblPOBM_current.PromisedDa, tblPOBM_baseline.PromisedDa; I know it's long, but it might help someone to help me. Thanks. |
Thread Tools | |
Display Modes | |
|
|