Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Top 1 Subquery question

I have an existing query with a Top 1 subquery:

SELECT A.SSN, A.TestType, A.Modality, A.LIC, A.SWARating, qryScoreConverter.Code, qryScoreConverter.Code AS Listening_Score, A.DateTaken AS Listening_Date, A.TestType AS Listening_TestType, A.FormVersion AS Listening_Form
FROM Proficiency AS A INNER JOIN qryScoreConverter ON A.SWARating = qryScoreConverter.SWACode
WHERE (((A.Modality)='Listening') AND ((A.LIC) In (15,39,120,183,326)) AND ((A.DateTaken)=(SELECT Top 1 S.DateTaken FROM Proficiency as S INNER JOIN Personnel ON S.SSN = Personnel.SSN WHERE (Personnel.WaiverGranted is not null OR (S.DateTaken>=#1/6/2009# AND S.DateTaken<=#1/6/2010#)) AND S.SSN = A.SSN AND S.LIC = A.LIC AND S.Modality = A.Modality ORDER BY S.DateTaken Desc, S.SWARating Desc)))
ORDER BY A.SSN, A.LIC;

Depending on the data, the Top 1 subquery can produce multiple records which makes the query fail (I had thought that the top 1 would handle that, but it doesn't).  Based on the related question I attempted to encapsulate the subquery in its own subquery: but access doesn't seem to be able to handle the WHERE clause with it's reference to the main query:

SELECT A.SSN, A.TestType, A.Modality, A.LIC, A.SWARating, qryScoreConverter.Code, qryScoreConverter.Code AS Listening_Score, A.DateTaken AS Listening_Date, A.TestType AS Listening_TestType, A.FormVersion AS Listening_Form
FROM Proficiency AS A INNER JOIN qryScoreConverter ON A.SWARating = qryScoreConverter.SWACode
WHERE (((A.Modality)='Listening') AND ((A.LIC) In (15,39,120,183,326)) AND (A.DateTaken=(SELECT Top 1 * FROM (SELECT S.DateTaken FROM Proficiency as S2 ORDER BY S2.DateTaken Desc, S2.SWARating Desc)  INNER JOIN Personnel ON S2.SSN = Personnel.SSN WHERE (Personnel.WaiverGranted is not null OR (S2.DateTaken>=#1/6/2009# AND S2.DateTaken<=#1/6/2010#)) AND S2.SSN = A.SSN AND S2.LIC = A.LIC AND S2.Modality = A.Modality )))
ORDER BY A.SSN, A.LIC;

Any suggestions?
0
deedub84
Asked:
deedub84
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
TOP N is a bit misleading.  Depending on Sorting ... if more than one record from the RESULT set 'tie' for the Nth position, more than one record will be returned for that position.

mx
0
 
ralmadaCommented:
try using "IN" instead of "=" operator
...((A.DateTaken) IN (SELECT Top 1 ....

SELECT A.SSN, A.TestType, A.Modality, A.LIC, A.SWARating, qryScoreConverter.Code, qryScoreConverter.Code AS Listening_Score, A.DateTaken AS Listening_Date, A.TestType AS Listening_TestType, A.FormVersion AS Listening_Form
FROM Proficiency AS A INNER JOIN qryScoreConverter ON A.SWARating = qryScoreConverter.SWACode
WHERE (((A.Modality)='Listening') AND ((A.LIC) In (15,39,120,183,326)) AND ((A.DateTaken)in(SELECT Top 1 S.DateTaken FROM Proficiency as S INNER JOIN Personnel ON S.SSN = Personnel.SSN WHERE (Personnel.WaiverGranted is not null OR (S.DateTaken>=#1/6/2009# AND S.DateTaken<=#1/6/2010#)) AND S.SSN = A.SSN AND S.LIC = A.LIC AND S.Modality = A.Modality ORDER BY S.DateTaken Desc, S.SWARating Desc)))
ORDER BY A.SSN, A.LIC;

Open in new window

0
 
GRayLCommented:
With multiple criteria in the WHERE clause, you could very well have several of the combinations with the same date, hence multiple records.  You have to decide the top 1 of what combination?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
From the Help File (partial):

"Typically, you use the TopValues property setting together with sorted fields. The field you want to display top values for should be the leftmost field that has the Sort box selected in the query design grid. An ascending sort returns the bottommost records, and a descending sort returns the topmost records. If you specify that a specific number of records be returned, all records with values that match the value in the last record are also returned."
                        ^^^^^^^^^^^^^^^^^

mx
0
 
MikeTooleCommented:
I'd be very tempted to encapsulate the DateTaken logic in a Function and use that in SQL:
...
AND A.DateTaken= GetDateTaken(A.SSN, A.LIC, A.Modality)
...




0
 
deedub84Author Commented:
In order to avoid the issue of the 'tie' that MX mentioned, I added an autonumber field to the table and added it to the subquery in order to avoid a tie.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now