OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   Running & Setting Up Queries (http://www.officefrustration.com/forumdisplay.php?f=25)
-   -   duplicate results in query (http://www.officefrustration.com/showthread.php?t=1119192)

pat67 June 3rd, 2010 02:24 PM

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.

pat67 June 3rd, 2010 02:28 PM

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

KARL DEWEY June 3rd, 2010 03:58 PM

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
.


pat67 June 3rd, 2010 04:18 PM

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?

pat67 June 3rd, 2010 07:11 PM

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

KARL DEWEY June 3rd, 2010 11:18 PM

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
.


pat67 June 7th, 2010 07:55 PM

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


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

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
OfficeFrustration.com