Find the first occurrence of multiple record entries

[AccessNoob]

As my screen name implies, I am a new to Access and I’m seeking some input to resolve an issue. I have tried numerous queries, read MSDN forums and Access help w/o success. I hope someone would have some ideas.

See ATTACHMENT for screen shot of the following scenario:

Two tables in a one-to-many relationship. The parent table (tblAdmByWard) contains the autonumber primary key (PatientID) and additional fields that will not change while the child table (tblContStayEval) has a corresponding field (i.e. PatientID) to secure referential integrity PLUS additional fields as shown that are evaluated and updated daily. During the update the current date is entered (DateOfReview) along with additional info thus creating a new daily record in the child table.

I would like to query the child table (tblContStayEval) to find and return ONLY the first occurrence (DateOfReview) that the criteria (Variance) was selected…i.e.2/24/2008 as illustrated in the screen shot. This needs to occur for all (PatientID) records but exclude any subsequent and/or similar selections. By “similar” I mean the date will be different for each record in the child table while the data in the other associated fields may or may not be.

I hope I explained this okay as I’m not sure how to go about this. Any ideas or thoughts would be appreciated.

 

[ajetrumpet]

You're going to need something like this:

SELECT tblAdmByWard.PatientID, [B][U]FIRST[/U][/B](YourOtherTable.[date of review])

FROM YourOtherTable INNER JOIN tblAdmByWard on
   tblAdmByWard.PatientID = YourOtherTable.PatientID

GROUP BY tblAdmByWard.PatientID, YourOtherTable.variance

HAVING YourOtherTable.variance = -1

This might be a useful reference for this concept too:

http://www.access-programmers.co.uk/forums/showthread.php?t=135763


Also, keep in mind that the FIRST function going to return just that: The first record in the set. If the set is not in order, make sure to add an ORDER BY clause in the SQL statement.

 

[AccessNoob]

Thanks, that worked beautifully. My next task to research is limiting the user to the selection of one check box only per field in a datasheet view subform. That is...the most recent check box activation removes the check from any/all earlier selections.