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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Selecting Latest Record From Junction Table, Access 2016    
 
   
BruceM
post Sep 16 2019, 12:11 PM
Post#1


UtterAccess VIP
Posts: 7,985
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
 
nuclear_nick
post Sep 16 2019, 12:17 PM
Post#2



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


What happens if you try...

SQL
SELECT
tblCertPart.CP_PartID,
tblPart.PartNumber,
tblCertPart.CP_CertID,
Max(tblCertificate.CertDate) AS LastCertDate
FROM tblCert INNER JOIN ( tblPart INNER JOIN tblCertPart ON tblPart.PartID = tblCertPart.CP_PartID
) ON tblCertificate.CertID = tblCertPart.CP_CertID
GROUP BY PartID, PartNumber, CertID
ORDER BY tblPart.PartNumber;

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Sep 16 2019, 12:35 PM
Post#3


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


Thanks Nick, but that gives me the same thing I had before. I changed the grouping fields to tblCertPart.CP_PartID and tblCertPart.CP_CertID. Adding CertID and PartID to the SELECT, and grouping on tblPart and tblCert, gives the same result.
Go to the top of the page
 
nuclear_nick
post Sep 16 2019, 01:05 PM
Post#4



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


Oh.

Re-read OP, and... oops.

SQL
SELECT
tblCertPart.CP_PartID,
tblPart.PartNumber,
tblCertPart.CP_CertID,
tblCertificate.CertDate
FROM tblCert INNER JOIN ( tblPart INNER JOIN tblCertPart ON tblPart.PartID = tblCertPart.CP_PartID
) ON tblCertificate.CertID = tblCertPart.CP_CertID
WHERE (CertDate = (SELECT Max(CertificationDate) AS MaxDate FROM tblCertificate)) GROUP BY PartID, PartNumber, CertID
ORDER BY tblPart.PartNumber;

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
orange999
post Sep 16 2019, 01:25 PM
Post#5



Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Another attempt (Note: I doubled up on the l in tbl eg tbll to avoid issues with my test table names)

SELECT TOP 1 tbllCertPart.CP_PartID
,tbllPart.PartNumber
,tbllCertPart.CP_CertID
,tbllCertificate.CertDate
FROM (
SELECT tbllCertPart.CP_PartID
,tbllPart.PartNumber
,tbllCertPart.CP_CertID
,tbllCertificate.CertDate
FROM tbllCertificate
INNER JOIN (
tbllPart INNER JOIN tbllCertPart ON tbllPart.PartID = tbllCertPart.CP_PartID
) ON tbllCertificate.CertID = tbllCertPart.CP_CertID
ORDER BY tbllPart.PartNumber
)
This post has been edited by orange999: Sep 16 2019, 01:26 PM

--------------------
Good luck with your project!
Go to the top of the page
 
BruceM
post Sep 16 2019, 01:48 PM
Post#6


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


Hi Nick,
Here is the latest, formatted for easier reading. I'll just mention that I should have stayed with tblCert (the real name) instead of tblCertificate, and PN instead of PartNumber in tblPart. I thought I was making it clearer, but I didn't change the text everywhere, so it got confusing. Anyhow, now it selects only the records from the latest date of any records.

For a little more detail, in practice the cert record is the one being actively entered, with related records for part numbers (PNs) since more than one PN can be on a certificate. The tblParts listing is more or less static, with new records being added only as new PNs show up.

The trouble is that I can select the latest CertDate for a given CP_PartID value (that is, a part number), but do not have a ready way to associate that value with a specific CertID value. I thought a subquery on the CertID value would get it done, but if so I haven't been able to construct it yet.

The best idea I have so far is to get the max date/PartID combination for each record, save the query, then join on those two values in another query, but it doesn't seem ideal.
CODE
SELECT
  tblCertPart.CP_PartID,
  tblPart.PN,
  tblCertPart.CP_CertID
FROM
  tblCert
    INNER JOIN
      (
        tblPart
          INNER JOIN tblCertPart
          ON tblPart.PartID = tblCertPart.CP_PartID
      )
    ON tblCert.CertID = tblCertPart.CP_CertID
WHERE
  tblCert.CertDate = (SELECT Max(CertDate) AS MaxDate FROM tblCert)
GROUP BY
  tblCertPart.CP_PartID,
  tblPart.PN,
  tblCertPart.CP_CertID
ORDER BY
  tblPart.PN;

Here is an expanded part number list. I should have provided sample data for more than one PN.
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
23456          888-AB     1324         01/05/2017            
23456          888-AB     1527         02/06/2018
23456          888-AB     1601         09/13/2019

Go to the top of the page
 
BruceM
post Sep 16 2019, 02:40 PM
Post#7


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


Thanks, Orange, but that returned just one record. Here is what I have so far. It works, but it feels like the long way around. If so, I haven't found the shortcut yet.

The grouping in the final query is to eliminate multiple appearances of a PN when there was more than one cert on the max date.

CODE
qryPN_AllCerts:
SELECT
  tblCertPart.CP_CertID,
  tblCertPart.CP_PartID,
  tblPart.PN,
  tblCert.CertDate
FROM
  tblCert
    INNER JOIN
      (
        tblPart
          INNER JOIN tblCertPart
          ON tblPart.PartID = tblCertPart.CP_PartID
      )
    ON tblCert.CertID = tblCertPart.CP_CertID
ORDER BY
  tblPart.PN,
  tblCert.CertDate;

qryPN_LatestCert:
SELECT
  tblCertPart.CP_PartID,
  Max(tblCert.CertDate) AS LatestCert
FROM
  tblCert
    INNER JOIN tblCertPart
    ON tblCert.CertID = tblCertPart.CP_CertID
GROUP BY
  tblCertPart.CP_PartID;

qryCertPartLatest:
SELECT
  Max(CP_CertID) AS CertMax,
  PN,
  CertDate
FROM
  qryPN_LatestCert
    INNER JOIN qryPN_AllCerts
    ON
      (
        qryPN_LatestCert.LatestCert = qryPN_AllCerts.CertDate
      )
    AND
      (
        qryPN_LatestCert.CP_PartID = qryPN_AllCerts.CP_PartID
      )
GROUP BY
  PN,
  CertDate
ORDER BY
  PN;



Go to the top of the page
 
orange999
post Sep 16 2019, 05:08 PM
Post#8



Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Yes, I thought the test data was a bit limited--only 1 tblCertPart.CP_PartID.






--------------------
Good luck with your project!
Go to the top of the page
 
projecttoday
post Sep 16 2019, 08:06 PM
Post#9


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


Why not use SELECT TOP 1?

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 17 2019, 06:27 AM
Post#10


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


QUOTE
Why not use SELECT TOP 1?

I'm looking for the latest certificate for each part number. I tried to devise a TOP 1 subquery, but was not able to figure it out after a number of attempts.
Go to the top of the page
 
cheekybuddha
post Sep 17 2019, 06:38 AM
Post#11


UtterAccess VIP
Posts: 11,684
Joined: 6-December 03
From: Telegraph Hill


Have not read through thoroughly, so apologies if you have already tried this:
CODE
SELECT
  cp.CO_PartID,
  p.PartNumber,
  cp.CertificateID,
  c.CertDate
FROM (
  SELECT
    CertificateID,
    MAX(CertificateDate) CertDate
  FROM tblCertificate
  GROUP BY CertificateID
) c
INNER JOIN tblCertPart cp
        ON c.CertificateID = cp.CP_CertID
INNER JOIN tblPart p
        ON cp.CP_PartID = p.PartID
;



Scratch the above! blush.gif

Will have another think if I get some moments later.



hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
nuclear_nick
post Sep 18 2019, 06:08 AM
Post#12



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


Just to understand... you have the below...

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
23456          888-AB     1324         01/05/2017            
23456          888-AB     1527         02/06/2018
23456          888-AB     1601         09/13/2019


And you want...

CODE
CP_PartID      PN             CertDate
12345          987-65     07/11/2019
23456          888-AB     09/13/2019


That look correct?




--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Sep 18 2019, 06:26 AM
Post#13


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


Yes, that is correct, with the addition that the CP_CertID value needs to be included. In the case of PN 987-65 it would be the 1561 value. The list is for a combo box that uses standard FindFirst coding to bring the user to the CertID record associated with the selection.
Go to the top of the page
 
projecttoday
post Sep 18 2019, 07:42 AM
Post#14


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


Try this (qryCertPartSelection).
Attached File(s)
Attached File  CertPartdb.zip ( 22.89K )Number of downloads: 6
 

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 18 2019, 12:53 PM
Post#15


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


Thanks, Robert! I see where the logic was eluding me during my attempts to use a subquery. However, there are some things that perhaps I should have mentioned. There are about 60,000 Cert records, with an average of about 6,000 added per year. And tblPart is in another database. While everything is properly indexed, or at least reasonably close, I don't think that helps much when there is a join across databases.

I tried the subquery route, but it bogged down. When I reduced the recordset to about 500 records it ran correctly, but took about ten seconds to open. I suspect having one of the tables in another database is part of the problem, but also I think having a query as the basis for a query with a subquery involves a lot of processing.

I realized after I posted that I want to include another table (Customers) to add the Customer Name field to the result. I discovered when I added the table to qryPN_LatestCert as shown in an earlier posting, opening qryCertPartLatest was rather slow. When I added the Customer table to qryPN_AllCerts it was much faster to open qryCertPartLatest. I'm still hoping to find a more efficient way to use the subquery, but for now it works quite smoothly.

Thanks for your help on this, Robert, and to Nick also for the ideas and suggested SQL.
Go to the top of the page
 
projecttoday
post Sep 18 2019, 01:31 PM
Post#16


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


So you're saying my query returns the correct records but it's slow on large tables? I would have been surprised it it wasn't slow on large tables. The solution to that, of course, is to use a temporary table (s) and break it down.

--------------------
Robert Crouser
Go to the top of the page
 
nuclear_nick
post Sep 19 2019, 05:44 AM
Post#17



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


I just imagined what it would have been like 'old school'...

Secretary asked to pull latest certifications on all parts associated with 'x'. The secretary goes to the cabinet and pulls the paperwork on 'x', to find all the associated parts, then goes to the parts filing cabinet and looks at the file for each part, finding the certification for each, checking the dates to make sure the latest one is pulled. (Not bad if you have one 'x'... but when you have 50,000... yikes.)

Then how to query a database like that? Fun stuff, because sometimes that requires working backwards, or from the inside out, like finding the latest certifications, then solving for 'x' after you have all the dates.

Part of what makes this job... fun. (One of my favorite moments of the past 20 years... Hurricane Katrina. Had to find out what product was where for the stores in the affected area... how much was in the stores, on the way to the stores... packed but not shipped yet... ordered but not packed yet... It was my first time I had to do something like that. What a challenge!)

Good luck, Bruce!

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Sep 19 2019, 06:32 AM
Post#18


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


Robert, I should have mentioned the table size. I have done plenty of subqueries, but not with a three-table join, or at least not with such a join and this many records. However, with records being added at the rate sometimes of 30 or more on one day, entered by several different people, managing a temp table would have been difficult. I suppose I could have done a temp table of everything before today, and a union with the subquery for today's records, or something like that. I have found a solution (described several posts ago) that works quickly enough. If not, I may try the union query solution, or maybe I will think of another approach. Maybe I could copy the Parts table as a local temp table, and join on that rather than across databases, since that table will not change nearly as often as the Cert table. Or maybe make the CertID field into one that is incremented manually to assure the largest number is the latest record. It may be more efficient than searching for the latest date.

I am keeping an eye on the eventual goal of moving the BE to SQL Server. Perhaps this type of processing will be more efficient in that environment, where I can create a view and link to that. Or maybe not. One thing for sure is that I am learning new things. Thanks again!
Go to the top of the page
 
projecttoday
post Sep 19 2019, 06:55 AM
Post#19


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


A temp table is for reporting. You don't maintain it. It should solve your problem.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 19 2019, 07:40 AM
Post#20


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


I'm trying to maintain the row source for a drop-down list that includes the most recent records. I should have mentioned that in the orginal posting, but did not say so specifically until post #13.

But the temp table suggestion gives me an idea. Maybe the users don't need all of today's records to be included, in which case I could create the temp table on startup, using whatever records are available at that time.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 12:43 AM