Display the Date of Last Record Entered by Each User 2

[djs45uk]

I have two tables with one-to-many relationship. One is called Staff and the other is Observation.

Staff enter observations into the Observation table.

I would like a form which displays a list of each staff member and the date they last entered an Observation.

There is a field in the Observation table called observeDate but I need the query to pull out the most recent observeDate for each member of staff.

Lothario has kindly suggested using the following query but it requires the user to enter the staffID at run time.

SELECT t1.StaffName,t2.ObserveDate
FROM Staff t1, Observation t2
WHERE t1.StaffId=T2.StaffId AND t2.ObserveDate In (select top 1 t3.ObserveDate from Observation t3 where t3.StaffID = t2.StaffID ORDER BY t3.ObserveDate Desc);

I actually want to display a list of all staff on a form and a date for each.

Many Thanks
Daniel

 

[PHV]

SELECT t1.StaffName, Max(t2.observeDate) As LastobserveDate
FROM Staff t1 INNER JOIN Observation t2 ON t1.StaffId = t2.StaffId
GROUP BY t1.StaffName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[formerTexan]

Hi,

Use the AGGREGATE funtions available to the query. Try GROUP BY on the StaffName field and then you might try MAX for the ObserveDate field rather than a subquery.

Cheers
Bill

 

[Lothario]

I am not sure why it requires the user to enter the staffID at run time??

-L

 

[djs45uk]

Thank you PVH! I have tried your solution (below) and it works but I get the same problem: it prompts me to enter the staffID at runtime. More specifically, it asks for t2.staffID at runtime. If I type in a staffID it works.

SELECT t1.staffFullname, Max(t2.observeDate) As LastobserveDate
FROM Staff t1 INNER JOIN Observation t2 ON t1.staffID = t2.staffID
GROUP BY t1.staffFullname;

Any ideas on how I could extend it to all staff?

 

[formerTexan]

Hi,

WHat is "Observation" in your SQL string"? Also just to be sure; are T1 and T2 tables rather than queries?

Cheers,
Bill

 

[PHV]

What is the name of the StaffID foreign key in the Observation table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Lothario]

formerTexan,

Observation is the table name and T1 and T2 are the alias names for table Staff

Hope that this clears things for you

-L

 

[formerTexan]

Sorry, I didn't go to the beginning to reread your original SQL and the indication you are using alias'.
Do you have any particular necessity for using the alias'? Otherwise they only obscure matters.

There isn't anything in the SQL statement that would prompt for a criteria. where are you getting this prompt: when you change the query from design to datasheet view, or when the query is called from a form? In other words, where is the SQL statement being used and in what context are you getting the criteria prompt?

Cheers,
Bill

 

[djs45uk]

Thank you all!

It was prompting for a value at runtime because I had typed the foreign key wrongly. --Sorry!!

Please, please help with one more thing:

I would like the form to include a bit more information than just the staffFullname and observeDate. How can I add more fields to the aggregate function?

I would like the form to display something like this:

[staffFullname] last entered record on [observeDate] about[observeStudent] in [observeSubject].

Joe Bloggs last entered a record on 11/11/04 about Sam Smith in History.

 

[formerTexan]

Hi,

Don't know how your tables are arranged for this endeavor, so..

Rather than trying to see how much can be done with a single query, you might tackle it in 2 queries:

1. Joe Bloggs last entered a record on 11/11/04
2. Sam Smith in History.

Pass the 2nd half of the results to a linked subform or list box.

Cheers,
Bill

 

[djs45uk]

Thank you Bill! I'm sure that's great advice but I don't really understand. I'll play aroundd with it and see if I can do as you have suggested.

Dan