One-to-many query problem 1

[JBlair]

I have 2 tables, Profiles and Immunizations. Profiles stores data on a group of personnel; Immunizations stores the imm data for those people.

In the Profiles table, FullName is that primary key and it is the foreign key in the Immunizations table. The immunizations table can have multiple rows for each person in the profiles table.

Here are the fields of interest:
Profiles.FullName
Immunizations.FullName
Immunizations.DateDue

The query itself is rather long and I do not think I need to post the entire thing to fix this issue, because it was working fine before I tried to introduce the immunizations data.

Where I'm having trouble is when trying to return only the soonest due immunization for each person. Right now my query is returning multiple rows for each person. When I tried using a subquery with SELECT TOP 1, the query got stuck in an endless loop and I crashed Access. Fun stuff

Any help out there for me? Thank you very much in advance.

 

[dhookom]

You can do this with a couple queries. The first selects the min of DateDue by FullName

SELECT FullName, Min(DateDue) as NextDate
FROM Immunizations
WHERE DateDue >= Date()
GROUP BY FullName;

Then combine this query with above tables in a query that joins the FullName and NextDate to FullName and DateDue.

Duane
Hook'D on Access
MS Access MVP

 

[JBlair]

What you gave me didn't work exactly as needed, but you definitely paved the path for me to figure it out. Thank you ever so much!

Here was my final result:

SELECT Immunizations.FullName, Min(Immunizations.DateDue) AS NextDate
FROM Immunizations
WHERE ((((Immunizations.ImmName) Like "*Hep*" Or (Immunizations.ImmName) Like "*Tetan*" Or (Immunizations.ImmName) Like "*Influ*") And (Immunizations.ImmName) Not Like "[*][*]*"))
GROUP BY Immunizations.FullName;