Hello,

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

Have you tried the TOP predicate?

SELECT TOP 1 ...

I may have at some point...Is that the function that allows you to choose like the top 25%, top 5, etc? I did try that... I am open to any suggestions at this point

Have you tried adding it to that query? If so, what happened that shouldn't have?

I tried it at the beginning of the query several different ways: SELECT TOP 1, SELECT TOP 10 PERCENT, etc...none of which work for what I need. If I use the SELECT TOP predicates with this query I lose other older records that are not of the same record; which I want to keep. See, the problem is that there is nothing that really distinguishes the records as being an older version of the same record (because some of the fields are changed)...any other ideas?

Are you looking for this type of thing?

http://support.microsoft.com/kb/210039/en-us

Actually something like that might work...although I was kinda hoping that I could just use SQL for it

The first method is pure SQL. I use that technique in a couple of applications.