UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Selecting Latest Record From Junction Table, Access 2016    
 
   
BruceM
post Sep 16 2019, 12:11 PM
Post#1


UtterAccess VIP
Posts: 7,987
Joined: 24-May 10
From: Downeast Maine


My brain seems to be stuck. I have three tables (showing main fields only):

CODE
tblCertificate
  CertificateID (autonumber PK)
  CertificateDate (date/time)

tblPart
  PartID (autonumber PK)
  PartNumber

tblCertPart (junction table)
  CertPartID (autonumber PK)  
  CP_CertID (linking field to tblCert)
  CP_PartID (linking field to tblPart)

Here is a representative selection of data from a query based on all three tables:

CODE
CP_PartID      PN            CP_CertID    CertDate
12345          987-65     1219          12/21/2016
12345          987-65     1546          02/06/2017
12345          987-65     1559          03/08/2018
12345          987-65     1560          07/11/2019
12345          987-65     1561          07/11/2019

This is repeated for many other PartIDs. Sometimes there is just one certificate for a part, but most often it is several or many. What I am trying to do is limit the query to showing the row with the CP_CertID value 1561, the latest Certificate for the part. The same date appears twice because sometimes there is a "tie", but it doesn't matter which one is selected. I haven't been able to devise a subquery. Grouping gets me the correct number of records, but only by using Max on the CP_CertID value, which is autonmber so cannot be guaranteed to show the highest value for the latest record.

Here is the SQL that returns the recordset shown above, along with similar data for other parts:
CODE
SELECT
  tblCertPart.CP_PartID,  
  tblPart.PartNumber,
  tblCertPart.CP_CertID,
  tblCertificate.CertDate
FROM
  tblCertificate
    INNER JOIN
      (
        tblPart
        INNER JOIN tblCertPart
        ON tblPart.PartID = tblCertPart.CP_PartID
      )
      ON tblCertificate.CertID = tblCertPart.CP_CertID
ORDER BY
      tblPart.PartNumber
Go to the top of the page
 

Posts in this topic
- BruceM   Selecting Latest Record From Junction Table   Sep 16 2019, 12:11 PM
- - nuclear_nick   What happens if you try... SQLSELECT tblCert...   Sep 16 2019, 12:17 PM
- - BruceM   Thanks Nick, but that gives me the same thing I ha...   Sep 16 2019, 12:35 PM
- - nuclear_nick   Oh. Re-read OP, and... oops. SQLSELECT tblCertP...   Sep 16 2019, 01:05 PM
- - orange999   Another attempt (Note: I doubled up on the l in tb...   Sep 16 2019, 01:25 PM
- - BruceM   Hi Nick, Here is the latest, formatted for easier ...   Sep 16 2019, 01:48 PM
- - BruceM   Thanks, Orange, but that returned just one record....   Sep 16 2019, 02:40 PM
|- - orange999   Yes, I thought the test data was a bit limited--on...   Sep 16 2019, 05:08 PM
|- - projecttoday   Why not use SELECT TOP 1?   Sep 16 2019, 08:06 PM
|- - BruceM   QUOTE Why not use SELECT TOP 1? I'm looking fo...   Sep 17 2019, 06:27 AM
|- - cheekybuddha   Have not read through thoroughly, so apologies if ...   Sep 17 2019, 06:38 AM
- - nuclear_nick   Just to understand... you have the below... CODEC...   Sep 18 2019, 06:08 AM
- - BruceM   Yes, that is correct, with the addition that the C...   Sep 18 2019, 06:26 AM
- - projecttoday   Try this (qryCertPartSelection).   Sep 18 2019, 07:42 AM
- - BruceM   Thanks, Robert! I see where the logic was elu...   Sep 18 2019, 12:53 PM
- - projecttoday   So you're saying my query returns the correct ...   Sep 18 2019, 01:31 PM
- - nuclear_nick   I just imagined what it would have been like ...   Sep 19 2019, 05:44 AM
- - BruceM   Robert, I should have mentioned the table size. I...   Sep 19 2019, 06:32 AM
- - projecttoday   A temp table is for reporting. You don't main...   Sep 19 2019, 06:55 AM
- - BruceM   I'm trying to maintain the row source for a dr...   Sep 19 2019, 07:40 AM
- - projecttoday   You create the temp table in the report.   Sep 19 2019, 08:00 AM
- - BruceM   This situation has nothing to do with a report. I...   Sep 19 2019, 08:33 AM
- - nuclear_nick   Because of the large tables, I do think filling a ...   Sep 19 2019, 08:39 AM
- - projecttoday   Same difference. If you cannot get the performa...   Sep 19 2019, 08:54 AM
- - BruceM   I used a make table query based on the query that ...   Sep 20 2019, 02:52 PM



Custom Search


RSSSearch   Top   Lo-Fi    22nd November 2019 - 01:21 PM