Unique Values Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don - 06 Dec 2005 19:55 GMT

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

Signature

Don Rountree

Reply to this Message

Van T. Dinh - 06 Dec 2005 20:29 GMT

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

Signature

HTH
Van T. Dinh
MVP (Access)

>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
[quoted text clipped - 16 lines]
> have
> been. Any help would be useful if I haven't confused you. Thanks...

Reply to this Message

John Spencer - 06 Dec 2005 20:35 GMT

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.

Reply to this Message

Ruskin Hardie - 06 Dec 2005 20:37 GMT

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;

> 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
[quoted text clipped - 9 lines]
> 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...

Reply to this Message