Show Only Newest Version of Record

[msmith974]

Hello,

This is an Access 03 question...

I have a database that includes multiple versions of the same record (i.e.- all fields are the same except for a date field) with that being said, I would like to have the select query view only show the most current version of each record by using date field. How can I get the query to only show the newest version of each record? Any ideas would be greatly appreciated.

Thanks,

Matt

[Brandoe85]

I haven't worked with access for many, many years - but can you do sub queries? Sequel server like-

SELECT
...fields here
FROM table t
INNER JOIN
(
SELECT idField, MAX(dateField) AS 'maxDate' FROM table GROUP BY idField
) t1
ON t1.idField= t.idField
AND t1.maxDate = t.dateField
Basically, you grab the MAX() date in the subquery...join on that as long as your other info(assuming there is).

[msmith974]

Brando,

This is possibly a better explanation of what I am looking for.

I have an access database application with the following table & fields that I would like to query:

Table Name: DWGS
Fields: DWGS.Discipline, DWGS.Area/Bldg, DWGS.DWGS,
DWGS.DWG Number, DWGS.DWG Title, DWGS.DWG Rev Number, DWGS.Issued,
DWGS.Date Entered


The data types of all fields are Text Fields, except for DWGS.DWGS which is a hyperlink field, and except for DWGS.Date Entered which is a date field...

What I would like to do with a query is show only the newest occurence of each record in the database using the DWGS.Date Entered field. I have written the following SQL statement which does work somewhat but not exactly what I need as final output:

SELECT DWGS.Discipline, DWGS.Area/Bldg, DWGS.DWGS, DWGS.DWG Number, DWGS.DWG Title, DWGS.DWG Rev Number, DWGS.Issued, Max(DWGS.Date Entered) AS \MaxOfDate Entered]
FROM DWGS
GROUP BY DWGS.Discipline, DWGS.Area/Bldg, DWGS.DWGS, DWGS.DWG Number, DWGS.DWG Title, DWGS.DWG Rev Number, DWGS.Issued
HAVING (((DWGS.Discipline)="electrical") AND ((DWGS.Area/Bldg)="casthouse"))
ORDER BY Max(DWGS.Date Entered) DESC;

As you can see the SQL statement is searching for all records with the DWGS.Discipline= "electrical", and DWGS.Area/Bldg= "casthouse", and then using the Max function on the DWGS.Date Entered field to locate the newest date on each record, etc.

As I said, it does work... The problem is that the DWGS.DWGS field which is a hyperlink field, and the DWGS.Issued field, and DWGS.Date Entered field will be altered in the database over time eventhough all other fields will remain the same (the end user considers this the same record; eventhough I know the database does not). i.e.- They add a new record, add a new hyperlink to it, and a new date and consider it a new version of the older record. Basically, when I use my SQL statement above it returns what the end user feels are duplicate records (they are not duplicates in the database, but they just cannot understand that concept.) I need to find a way to have SQL/Access only show the newest records by date and hide older records (eventhough they are not identical records). I know this probably does not make any sense, but I do not know how else to explain it. Any suggestions would be greatly appreciated. Thank you very much in advance.

Matt