Help! Query to show distinct price records

[hardrock]

Hi all,

I have a table that contains the columns

Price item description date buyer

I am trying to sort the data in my table so it will only show the distinct records when there is a change in price.

So for example

Price item description date Buyer
£1.00 101 PC 3/3/9 hardrock
£1.00 101 PC 1/1/9 hardrock
£2.00 101 PC 12/12/8 hardrock
£2.00 101 PC 10/11/8 hardrock
£3.00 101 PC 11/10/8 hardrock

Would return a result of

Price item description date Buyer
£1.00 101 PC 1/1/9 hardrock
£2.00 101 PC 10/11/8 hardrock
£3.00 101 PC 11/10/8 hardrock

assuming the sort was done from earliest date first

The Result would be ok in a table or a query format.

Any ideas?

Thanks!

 

[lemo]

select query, like below, i think should work -

SELECT Table1.price, Table1.item, Table1.description, Min(Table1.date1) AS MinOfdate1, Table1.buyer
FROM Table1
GROUP BY Table1.price, Table1.item, Table1.description, Table1.buyer;

 

[hardrock]

No luck! To make things easier i've uploaded a database that contains a table of the typical data i want to sort. I only want to show records that shows a price change between two records,assuming the sort is done earliest date first. Thanks

 

[lemo]

so which rows would you like to show in you test.mbd, 1-8-9-15 ?

if so - i don't know how to do it in access (or it will take me forever to figure it out), wait for the masters to help you.

in the meantime, if you import your table into Excel, it will take a minute to accomplish the task. provided your upper left corner of the table is in A1, in the cell H2 enter a formula =IF(A2=A1,"",A2) and copy it down. then filter out blanks.

l