Need help with a query

[LJWW2]

Let me start by saying that I have very little experience in Access. I've
learned what I know by "tinkering" around in it and a lot of the terminology
I've seen on these boards is above my head.

Here's the background to my question. I created an employee database that
consists of one massive form with several tabs and on each tab there are
several subforms. The main form was created from the Employee Header table
and the subforms were created from queries from tables. The idea is when you
open the form, you see an employee's basic info on the top (i.e. ssn, name)
and then as you go through each of the tabs you see information specific to
that employee (i.e. employment dates, salary, address). Because employee
information can change frequently, each subform has an Effective Date field
specific to the information on that subform. I fixed it so that the most
recent effective date is displayed so that when you look at an employee in
the form, you are looking at "real time" data.

What I'm trying to do is to create a query that displays only the "real
time" data for each employee. What's happening now, for example, is if I run
a query and someone has more than one record in a subform, all of the records
are showing up and the employee's name is listed multiple times. I tried
doing a query using "=DMax("[Field Name]","[Table Name]")", but when I do
that, only the employee with the most recent effective date shows up. Can
anyone help?

Thank you in advance!

 

[ghetto_banjo]

When you build your query, right click inside the design view and turn
on Totals. Then you can set the Effective Date to Max, and keep the
EmployeeID or whatever as Group By. Be sure to use Max and NOT Last
for the Effective Date.

A (possibly) simplified version of the query's SQL will look like:

SELECT EmployeeID, Max([EffectiveDate] As MostRecentDate FROM
tblEmployees GROUP BY EmployeeID