listing records with only the newest date show.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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??
 
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.
 
Back
Top