View Single Post
  #2  
Old June 3rd, 2010, 02:28 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default duplicate results in query

On Jun 3, 9:24*am, pat67 wrote:
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.


Ok I got the different dates to show by using the above query to show
almost all of the fields. But the issue of the duplicate dates remains