Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Oct 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show Only Newest Version of Record

    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

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    I haven't worked with access for many, many years - but can you do sub queries? Sequel server like-
    Code:
    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).

  • #3
    New to the CF scene
    Join Date
    Oct 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •