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
|
|||
|
|||
challenging query
I have a table with a code field and a price field.
I need to find records where the code field data is the same (I did a duplicate query) Now I need to make a report that shows those duplicates but only if the price field is different. I am trying to show where there are duplicate codes that have different prices -- Thank you |
#2
|
|||
|
|||
challenging query
On Wed, 3 Mar 2010 23:03:01 -0800, Pellissier
wrote: I have a table with a code field and a price field. I need to find records where the code field data is the same (I did a duplicate query) Now I need to make a report that shows those duplicates but only if the price field is different. I am trying to show where there are duplicate codes that have different prices A "Self Join" query is the ticket here. Add your table to the grid twice; Access will alias the second instance by appending _1 to the name. Join Tablename.Code to Tablename_1.Code; include both Price fields; and put a criterion on Talbename_1.Price of [Tablename].[Price] You only need not - the reason is left as an exercise to the user. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
challenging query
On 4 mrt, 08:32, John W. Vinson
wrote: On Wed, 3 Mar 2010 23:03:01 -0800, Pellissier wrote: I have a table with a code field and a price field. I need to find records where the code field data is the same (I did a duplicate query) Now I need to make a report that shows those duplicates but only if the price field is different. I am trying to show where there are duplicate codes that have different prices A "Self Join" query is the ticket here. Add your table to the grid twice; Access will alias the second instance by appending _1 to the name. Join Tablename.Code to Tablename_1.Code; include both Price fields; and put a criterion on Talbename_1.Price of [Tablename].[Price] You only need not - the reason is left as an exercise to the user. -- * * * * * * *John W. Vinson [MVP] Does “duplicate” means that a code has a maximum of two occurrences? If not what to do with code/price combinations like: A / 1 A / 2 A / 2 (select A / 1 ?) or B / 1 B / 2 B / 1 B / 2 (select none) A query that does it could look like: SELECT Code, Price FROM T1 GROUP BY Code, Price HAVING Code In (SELECT Code FROM T1 GROUP BY Code HAVING Count(Price)1) AND Count(Price)=1; Groeten, Peter http://access.xps350.com |
#4
|
|||
|
|||
challenging query
This query would identify all cases where code field had more than one price.
SELECT CodeField FROM Table GROUP BY CodeField HAVING Min(Price) Max(Price) So you could use something like the following to identify the records. The only exceptions would be if you had one record for a code field with a price and one or more records for that code field with no price (null). SELECT * FROM [SomeTable] WHERE [CodeField] IN (SELECT [CodeField] FROM [SomeTable] GROUP BY [CodeField] HAVING Min([Price]) Max([Price])) Post back if you need further help in building the query. If needed you can handle nulls by modifying the having clause to read like the following. HAVING Min(Nz(Price,0)) Max(Nz(Price,0)) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Pellissier wrote: I have a table with a code field and a price field. I need to find records where the code field data is the same (I did a duplicate query) Now I need to make a report that shows those duplicates but only if the price field is different. I am trying to show where there are duplicate codes that have different prices |
Thread Tools | |
Display Modes | |
|
|