duplicate results in query

P

pat67

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.
 
P

pat67

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.
Click to expand...

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
 
K

KARL DEWEY

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 said:
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.
Click to expand...

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
.
Click to expand...
 
P

pat67

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 said:
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.
Click to expand...
Click to expand...
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 -
Click to expand...

- Show quoted text -
Click to expand...

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?
 
P

pat67

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.
Click to expand...
Unless you can distinguish between the two parts of the PO then you will
have to only use the most recent date.
Click to expand...
pat67 said:
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 problemis
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 -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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
 
K

KARL DEWEY

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 said:
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.
Click to expand...
Unless you can distinguish between the two parts of the PO then you will
have to only use the most recent date.
Click to expand...
:
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
Click to expand...
Vendor PO Line Qty Date
X 1234 1 2 7/1/2010
X 1234 1 2 8/1/2010
Click to expand...
When i combine this with the other table which is the same for these I
get the following
Click to expand...
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
Click to expand...
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.
Click to expand...
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;
Click to expand...
I know it's long, but it might help someone to help me. Thanks.
Click to expand...
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 -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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
.
Click to expand...
 
P

pat67

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 said:
On Jun 3, 10:58 am, KARL DEWEY <[email protected]>
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 numberof dates
in the second.
Unless you can distinguish between the two parts of the PO then youwill
have to only use the most recent date.
--
Build a little, test a little.
:
Hi, I am getting duplicate results when I run a query. Here arethe
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 donot
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 -
Click to expand...
Click to expand...
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.
Click to expand...
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 -
Click to expand...

- Show quoted text -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate Results in Query 0

Top