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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 . |
Thread Tools | |
Display Modes | |
|
|