• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • 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 Access MVP)Database ArchitectCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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