Selecteting from a table with multiple records

  • Thread starter Thread starter Anderson
  • Start date Start date
A

Anderson

Can you please help?

I have an appointment table and employees have multiple records in this
table simple because their contracts change. The table has fields change
date and reason for change amongst other fields. My problem is this; I
am trying to write a function, which will flag out a record that has a
change date at a given date ie (>= 01/01/04) the change reason should
also be one specified. However I want this flagged out on the current
record bearing in my mind that an employee only has one current
appointment at a give time.

Please help. Thank you in advance for your help.
 
Anderson

If I understand you correctly, I think you can do it like this...
1. Make a Totals Query to identify the change date of each employee's
current appointment. I think the SQL view of such a query will look like...
SELECT EmployeeID, Max([Change Date]) As LatestChange
FROM Appointment
GROUP BY EmployeeID
2. Make a new query including the Appointment table and also this first
query, joined on the EmployeeID field (or whatever you have called the
equivalent field in your database). Then, in the criteria of the Change
Date field in the query, something like this....
=#1/01/2004# And >=[LatestChange]
Click to expand...
 
Back
Top