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

On Jun 3, 10:58*am, KARL DEWEY
wrote:
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
.- Hide quoted text -


- Show quoted text -


That's not going to work so I need to work around this. What I have
come up with is to subtract the new date from the old date. What this
does is for the ones with only 1 line and same date, that returns 0.
For those with multiple lines but the same dates, Cartesian effect,
the subtotal for those will be 0. And for those that show correctly
different dates the subtotal will not be 0. What I need to do is
extract only those lines that do not equal 0 either alone or as a
subtotal of the PO and line. Do you understand what I mean?