challenging query

[Pellissier]

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

 

[John W. Vinson]

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.

 

[XPS350]

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.

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

 

[John Spencer]

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