Results 1 to 8 of 8
  1. #1
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24

    list all addresses and the latest date in datepassed field

    I need a select query that returns the latest PassedDate for every address in the checkvalve table, ie:
    Code:
    COMMERICAL    16    2014    WATTS    800M4QT    01126    7    EAST SIDE OF BLDG BY GAS MTR    Yes
    COMMERICAL    17    2019    WILKINS    975XL    111835    5    IN BASEMENT    Yes
    RESIDENTIAL    18    2014    WILKINS    720-A    G00302    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    29    2016    FEBCO    765-1    H20585    1    EAST SIDE OF HOUSE    Yes
    I have been pulling my hair out for the last couple of hours and have made no progress.
    Sample Data


    Code:
    Name    Address    PassedDate    Make    Model    SN    Size    Location    Passed
    COMMERICAL    16    2012    WATTS    800M4QT    01126    7    EAST SIDE OF BLDG BY GAS MTR    Yes
    COMMERICAL    16    2013    WATTS    800M4QT    01126    7    EAST SIDE OF BLDG BY GAS MTR    Yes
    COMMERICAL    16    2014    WATTS    800M4QT    01126    7    EAST SIDE OF BLDG BY GAS MTR    Yes
    COMMERICAL    16    2010    WATTS    800M4QT    01126    7    EAST SIDE OF BLDG BY GAS MTR    Yes
    COMMERICAL    16    2011    WATTS    800M4QT    01126    7    EAST SIDE OF BLDG BY GAS MTR    Yes
    COMMERICAL    17    2019    WILKINS    975XL    111835    5    IN BASEMENT    Yes
    RESIDENTIAL    18    2011    WILKINS    720-A    G00302    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    18    2012    WILKINS    720-A    G00302    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    18    2013    WILKINS    720-A    G00302    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    18    2014    WILKINS    720-A    G00302    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    29    2010    FEBCO    765-1    H20585    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    29    2011    FEBCO    765-1    H20585    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    29    2013    FEBCO    765-1    H20585    1    LEFT SIDE OF HOUSE    Yes
    RESIDENTIAL    29    2014    FEBCO    765-1    H20585    1    EAST SIDE OF HOUSE    Yes
    RESIDENTIAL    29    2016    FEBCO    765-1    H20585    1    EAST SIDE OF HOUSE    Yes

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A simple aggregate query use Max() should do that. However, if you want to return the entire record, that gets a little more complicated.

    Can be done with a nested query. Review http://allenbrowne.com/subquery-01.html#TopN

    Or build aggregate query with Max() that groups on Name and Address then join that query to table with compound join on Name, Address, PassedDate fields.

    Name is a reserved word and should not use reserved words as object names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming address is actually a foreign key then create a query

    Code:
    SELECT Address, max(PassedDate) AS Latest
    FROM myTable
    GROUP BY Address

    then link it to myTable on address and the date field

    Code:
    SELECT myTable.*
    FROM myTable 
                     INNER JOIN 
                             (SELECT Address, max(PassedDate) AS Latest
                             FROM myTable
                             GROUP BY Address) LP 
                                      ON myTable.Address=LP.Address AND myTable.PassedDate=LP.Latest

  4. #4
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    Thank you both for your help. The results are looking pretty good to me.

  5. #5
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    Ah feature creep, how I love you. The user has now asked for the report to be sorted by streetname. It makes it easier for the checkvalve tester to do their job by testing an entire street the same day. So I went back and tried to add that to the query. It would have worked except for the fact that the address table contains a lookup to the streets table. With the following query I am prompted to enter addnum and streetid. If I press enter both times the query runs, but doesn't sort.

    Code:
    SELECT checkvalves.*, (SELECT address.addressnumber FROM address WHERE checkvalves.address = address.id) AS addnum, (SELECT address.streets_id FROM address WHERE checkvalves.address = address.id) AS streetid
    FROM checkvalves INNER JOIN (SELECT checkvalves.address, max(checkvalves.PassedDate) AS Latest
      FROM checkvalves
      GROUP BY checkvalves.address)  AS LP ON (checkvalves.PassedDate=LP.Latest) AND (checkvalves.Address=LP.Address)
    ORDER BY checkvalves.PassedDate, streetid, addnum;
    I tried a left join but I get a syntax error:
    Code:
    SELECT checkvalves.*, address.*, streets.*
    FROM checkvalves INNER JOIN (SELECT checkvalves.address, max(checkvalves.PassedDate) AS Latest
      FROM checkvalves
      GROUP BY checkvalves.address)  AS LP ON (checkvalves.PassedDate=LP.Latest) AND (checkvalves.Address=LP.Address)
      LEFT JOIN streets ON (checkvalves.address = address.id AND address.streets_id = streets.id)
    ORDER BY checkvalves.PassedDate, streets.streetname, address.addressnumber
    Address Table
    Code:
    ID   AddressNumber   Street_ID (lookup to streets)
    1    10001               64
    2    10002               128
    3    9300                 34
    Streets table
    Code:
    ID   StreetName
    1    Main St
    2    2nd St
    3    Joliet St

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Most experienced developers will advise not to build lookups in tables. However, that should not prevent a query from working.

    Consider doing address grouping in report, not query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    always helps to provide the error message but my guess would be you are missing brackets is the FROM clause. Suggest build it in the query builder first so you can see what's what

  8. #8
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    I will try to remove the lookup later. As with most things I inherited this pile of code. I was able to figure most of it out using datasheet view. The rest I tweaked the SQL.
    Code:
    SELECT checkvalves.*, Address.*, Streets.Streetname
    FROM Streets INNER JOIN ((checkvalves INNER JOIN (SELECT checkvalves.address, max(checkvalves.PassedDate) AS Latest
      FROM checkvalves
      GROUP BY checkvalves.address)  AS LP ON (checkvalves.Address = LP.Address) AND (checkvalves.PassedDate = LP.Latest)) INNER JOIN Address ON checkvalves.address = Address.id) ON Streets.ID = Address.Streets_ID
    ORDER BY checkvalves.PassedDate, Streets.Streetname, Address.addressnumber;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Lookup a query field with the latest date
    By antioch in forum Modules
    Replies: 3
    Last Post: 08-28-2017, 08:30 PM
  2. List box no longer showing latest entry
    By vector39 in forum Access
    Replies: 6
    Last Post: 06-08-2017, 11:02 AM
  3. Replies: 8
    Last Post: 01-10-2016, 11:51 AM
  4. Replies: 4
    Last Post: 10-03-2015, 01:22 AM
  5. Replies: 4
    Last Post: 10-17-2014, 01:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums