Unique Values Query

[Guest]

I have a table with about 125,000 records. The fields consist of Customer
Number, Item Number and Price. Each record indicates a purchase by a
customer. Many of the purchases are for the same item, at the same price;
however, there are occasional price increases. Here is what I want to do. I
want to return every instance of customer purchases when the price is
different. For instance, if customer 1 bought item 2 for $3.00 a total of 10
times and then the price went to $4.00. I want the query to return 1 record
of customer 1 buying item 2 for $3.00 and 1 record of customer 1 buying item
2 for $4.00 (This I can do by selecting Unique Values in the query's
properties. Here's the kicker - I don't want the query to return any items
that have only had 1 price (For instance, 1 unique value) What I'm trying to
do is identify every customer that have had price changes and what they have
been. Any help would be useful if I haven't confused you. Thanks...

 

[Van T. Dinh]

Do you have a separate Table that hold the history of Prices?

 

[John Spencer]

Two query solution.

First query (Saved as qDistinct)
SELECT Distinct [Customer Number], [Item Number], [Price]
FROM YourTable.

SecondQuery
SELECT *
FROM QDistinct
WHERE [Customer Number] IN
(SELECT T.[Customer Number]
FROM QDistinct as T
GROUP BY T.[Customer Number]
HAVING Count(T.[Customer Number]) > 1)


If your field and table names don't have spaces or other special characters,
that can all be written as one query.

 

[Ruskin Hardie]

Single Query Solution;

SELECT Sales.Customer,
Sales.Item,
Sales.Price,
Sum(Sales.Quantity) AS TotalQuantity,
Sum([Price]*[Quantity]) AS TotalValue

FROM Sales

WHERE EXISTS (
SELECT ASales.Customer
FROM Sales AS ASales
WHERE ASales.Customer = Sales.Customer
AND ASales.Item = Sales.Item
AND ASales.Price <> Sales.Price
)

GROUP BY Sales.Customer, Sales.Item, Sales.Price;