Query to get Latest Status

[music_al]

Hi

I have an intermediate/junction table that records the status of a job candidate and the date that the status was reached. The logic is that the most recent date/time is the current status. So, I'd like to build a query and then pass this to a field in the Candidate table.

The intermediate/junction table is made up like this

Candidate_Status_ID
Candidate_ID
Status_ID
Status_Date

Ive created a simple query and in the Status_Date field Ive used the MAX function but its still showing all the dates relating to that candidate. See the attachment



How do I then pass that STATUS value (not the date) to a text box ?

 

[isladogs]

Remove the first and third fields from your query so you just have CandidateID and max date.

 

[music_al]

Thank you.

And how do I then pass the status to a text box on the parent form ?

 

[isladogs]

You could set the textbox control source using your query ...
or scrap the query and use a Dlookup based on the table with suitable filter criteria.

 

[music_al]

And how do I do either of those ?

 

[music_al]

I set the Source for the Textbox as the field in the query...

=[qry_Candidate_Status2]![Candidate_Status_Long]

but when I load the form the Textbox just shows...

#Name?

 

[isladogs]

Ummm ...not like that

Try something like one of these for the control source instead

= DLookup("MaxOfStatus_Date","tblCandidate_Status", "Candidate_ID = " & Me.Candidate_ID)

OR create your query qryCandidate_Status as

SELECT Max(Status_Date) AS MaxDate FROM tblCandidate_Status WHERE Candidate_ID = Forms!YourFormName.Candidate_ID

then use this as your control source

= DLookup("MaxDate","qryCandidate_Status")