Limit query to one record

[sotssax]

I have a database to keep records of teachers at a school. on a report, I need to limit professional development to ones with a date equal to or after their license was issued. I can do that. But for teachers who have multiple licenses it will duplicate the PD for each license issue date.

I have created a query that pulls the staffID from a demographics table, the professional development info form the PD table, and the license issued dates from the license table. On the license table the staffID may be listed twice if they have more than one license, are are usually issued on the same date.

How do I create a query that will just take the first license record for each staffID and pass it to another query so I can use ">= teacherLicense.DateIssued" for PD date?



Here is what I have tried so far:

SELECT [tbl_Teacher Licenses].[staffID], [tbl_Teacher Licenses].[date issued], [tbl_Teacher Licenses].[ID]
FROM [tbl_Teacher Licenses]
WHERE [tbl_Teacher Licenses].[ID] IN
(SELECT TOP 1 [tbl_Teacher Licenses].[ID]
FROM [tbl_Teacher Licenses] AS [Dupe]
WHERE [Dupe].[staffID]=[tbl_Teacher Licenses].[staffID]
ORDER BY [Dupe].[date issued] DESC, [Dupe].[ID] DESC);

Any help would be greatly appreciated.

[June7]

And what happens - error message, wrong results, nothing? Think maybe the inner query needs a join clause.

I construct nested queries by first building the innner query and saving it. When I get that right, I then build the outer using the saved query. Then I copy/paste the SQL of the first query into the second to result in nested query. Was that your process?

[sotssax]

Nothing. All that happens is it shows me all the records. The problem maybe that each staff can have multiple licenses but they all have the same date. So when is looking for the top record it is seeir the same date and doesn't know which 1 to choose.

[sotssax]

Solved:

Here is what I did.

1. I created a query to limit records for each Staff ID to a Max Date, even though all records for each Staff ID had the same date. Here is the SQL statement:

SELECT Max([Teacher Licenses].[date issued]) AS MaxDate, [Teacher Licenses].staffID
FROM [Teacher Licenses]
GROUP BY [Teacher Licenses].staffID;

2. On my PD query I added the Teacher Licenses Query through the Show Tables menu option. I then entered this criteria for my Course Date field:
>={Teacher Licenses Query].[MaxDate]

Worked like a charm.

I have to say that it was pbaldy's website that helped me find the answer.

http://www.baldyweb.com/LastValue.htm