A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

duplicate results in query



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 02:24 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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.
Ads
  #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
  #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
.

  #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?
  #5  
Old June 3rd, 2010, 07:11 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default duplicate results in query

On Jun 3, 11:18*am, pat67 wrote:
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?- Hide quoted text -

- Show quoted text -


I have thought about a rnaking query from the first two tables but the
result doesn't work right. I am looking to get each occurence of a PO
and line ranked as 1,2,3 etc depending on how many times the line was
split. Not sure how to do it though. here is that query, but it
returns 0,1 2 or 3. for each line it's just counting.

SELECT a.Vendor, a.[Vendor name], a.[Purch Doc], a.Item, a.Rel,
a.OTyp, a.MRPC, a.Material, a.[Short Text], a.PromisedQt, a.[PD
release], a.[Del Date], a.PromisedDa, a.[Resch date], a.Exc, a.CC,
a.Price, a.Value
(SELECT Count(Item) FROM tblPOBM_baseline AS b WHERE b.Vendor =
a.Vendor AND b.[Vendor Name] = a.[Vendor Name] AND b.[Purch Doc] = a.
[Purch Doc] AND b.Item = a.Item AND b.Rel = a.Rel AND b.OTyp = a.OTyp
AND b.MRPC = a.MRPC AND b.Material = a.Material AND b.[Short Text] = a.
[Short Text] AND b.PromisedQt = a.PromisedQt AND b.[PD release] = a.
[PD release] AND b.[Del Date] = a.[Del Date] AND b.PromisedDa =
a.PromisedDa AND b.[Resch date] = a.[Resch date] AND b.Exc = a.Exc AND
b.CC = a.CC AND b.Price = a.Price AND b.Value = a.Value) AS Rank
FROM tblPOBM_baseline AS a
  #6  
Old June 3rd, 2010, 11:18 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default duplicate results in query

Revise to add 1 --
.....b.CC = a.CC AND b.Price = a.Price AND b.Value = a.Value)+1 AS Rank

--
Build a little, test a little.


"pat67" wrote:

On Jun 3, 11:18 am, pat67 wrote:
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?- Hide quoted text -

- Show quoted text -


I have thought about a rnaking query from the first two tables but the
result doesn't work right. I am looking to get each occurence of a PO
and line ranked as 1,2,3 etc depending on how many times the line was
split. Not sure how to do it though. here is that query, but it
returns 0,1 2 or 3. for each line it's just counting.

SELECT a.Vendor, a.[Vendor name], a.[Purch Doc], a.Item, a.Rel,
a.OTyp, a.MRPC, a.Material, a.[Short Text], a.PromisedQt, a.[PD
release], a.[Del Date], a.PromisedDa, a.[Resch date], a.Exc, a.CC,
a.Price, a.Value
(SELECT Count(Item) FROM tblPOBM_baseline AS b WHERE b.Vendor =
a.Vendor AND b.[Vendor Name] = a.[Vendor Name] AND b.[Purch Doc] = a.
[Purch Doc] AND b.Item = a.Item AND b.Rel = a.Rel AND b.OTyp = a.OTyp
AND b.MRPC = a.MRPC AND b.Material = a.Material AND b.[Short Text] = a.
[Short Text] AND b.PromisedQt = a.PromisedQt AND b.[PD release] = a.
[PD release] AND b.[Del Date] = a.[Del Date] AND b.PromisedDa =
a.PromisedDa AND b.[Resch date] = a.[Resch date] AND b.Exc = a.Exc AND
b.CC = a.CC AND b.Price = a.Price AND b.Value = a.Value) AS Rank
FROM tblPOBM_baseline AS a
.

  #7  
Old June 7th, 2010, 07:55 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default duplicate results in query

On Jun 3, 6:18*pm, KARL DEWEY
wrote:
Revise to add 1 --
....b.CC = a.CC AND b.Price = a.Price AND b.Value = a.Value)+1 AS Rank

--
Build a little, test a little.



"pat67" wrote:
On Jun 3, 11:18 am, pat67 wrote:
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?- Hide quoted text -


- Show quoted text -


I have thought about a rnaking query from the first two tables but the
result doesn't work right. I am looking to get each occurence of a PO
and line ranked as 1,2,3 etc depending on how many times the line was
split. Not sure how to do it though. here is that query, but it
returns 0,1 2 or 3. for each line it's just counting.


SELECT a.Vendor, a.[Vendor name], a.[Purch Doc], a.Item, a.Rel,
a.OTyp, a.MRPC, a.Material, a.[Short Text], a.PromisedQt, a.[PD
release], a.[Del Date], a.PromisedDa, a.[Resch date], a.Exc, a.CC,
a.Price, a.Value
(SELECT Count(Item) FROM tblPOBM_baseline AS b WHERE b.Vendor =
a.Vendor AND b.[Vendor Name] = a.[Vendor Name] AND b.[Purch Doc] = a.
[Purch Doc] AND b.Item = a.Item AND b.Rel = a.Rel AND b.OTyp = a.OTyp
AND b.MRPC = a.MRPC AND b.Material = a.Material AND b.[Short Text] = a.
[Short Text] AND b.PromisedQt = a.PromisedQt AND b.[PD release] = a..
[PD release] AND b.[Del Date] = a.[Del Date] AND b.PromisedDa =
a.PromisedDa AND b.[Resch date] = a.[Resch date] AND b.Exc = a.Exc AND
b.CC = a.CC AND b.Price = a.Price AND b.Value = a.Value) AS Rank
FROM tblPOBM_baseline AS a
.- Hide quoted text -


- Show quoted text -


That only adds 1 it makes the 0s 1, the 1s 2 and so on. What I am
looking to do is rank the occurrences of the Purch Doc and Item. Not
sure how to do that
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 OfficeFrustration.
The comments are property of their posters.