View Single Post
  #3  
Old June 3rd, 2010, 03:58 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default duplicate results in query

Your tables are joined on Purch Doc and Item but you have multiple dates in
both tables. This results in what is known as a Cartesian effect. The
number of dates in the first table will be multiplied by the number of dates
in the second.

Unless you can distinguish between the two parts of the PO then you will
have to only use the most recent date.

--
Build a little, test a little.


"pat67" wrote:

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
.