listing records with only the newest date show.

A

adrian

Help! I have set up a database to monitor cost prices of materials and over
the last few years I have made multiple enteries of the same products so that
I can track a history of pricing, so I might have say 7 records all the same
only the date and price would be different.
This has been ok but now I have so many records it is hard to understand
quickly the system.
I want to be able to create a report so it pull all records up but where
there are multiple entries it only shows the newest with the latest price.

can anyone help me?
 
D

Duane Hookom

It would have helped if you had provided your table structure and name.

SELECT *
FROM tblPriceHistory
WHERE PriceDate =
(SELECT Max(PriceDate)
FROM tblPriceHistory ph
WHERE ph.MaterialID = tblPriceHistory.MaterialID);

You will need to substitute all your names for my guesses.
 
A

adrian

Sorry I am a little confused by your reply, when you ask for the table
structure how can put it here without typing it all aout?

I have actually got a result sort of? if I do a query and set the date to
last in total it works but if i include all the fields from the table it
mucks up because the prices are different so it won't sort them by date only,
I think this is due to the fact it will only do this on duplicate records and
as the prices are diferent they are not actually duplicate records. If i only
include fields that will have the same data apart form date, there is no
problem.


Can I have a query to sort out the duplicates on dates and then a form that
uses this data to display the rest of the details from the table?

I'm sorry if I confusing you? because I'm nearly confusing me??
 
D

Duane Hookom

You may need to type out all the significant field and table names.

BTW: There is rarely a good use of "LAST" in a query.
 
Top