By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

A2002: Count only records closest to criterion in query

P: n/a
I have a simple database comprised of two tables. tbl_employee_info
stores employee info (name, hire date, title, etc.) and
tbl_departmental_history stores departmental transfer history. The two
tables are linked via the EmployeeID, which is an autonumber.

What I need to be able to do is tell how many employees I have in which
departments for a selected month, but I'm having trouble with the
query.

For instance, in tbl_departmental_history
(EmployeeID,Department,TransferDate)

123......Department1......1/1/2004
123......Department2......4/1/2004
123......Department3......8/1/2004
123......Department4......12/1/2004

Let's say I want to get a department count for May. Some employees
have never changed, so I need to count everything from the beginning of
the year. But for those that have changed departments, if I include
everything, then they get counted multiple times in multiple
departments.

I can get a query that gives me department count for employees that
have never transferred, but how do I get a query that looks at the
above records, sees that the 4/1/2004 entry is the closest to May, and
only counts that record? Any help is greatly appreciated.

-Josh

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
jn********@yahoo.com wrote:
What I need to be able to do is tell how many employees I have in which
departments for a selected month, but I'm having trouble with the
query.

For instance, in tbl_departmental_history
(EmployeeID,Department,TransferDate)

123......Department1......1/1/2004
123......Department2......4/1/2004
123......Department3......8/1/2004
123......Department4......12/1/2004

Let's say I want to get a department count for May. Some employees
have never changed, so I need to count everything from the beginning of
the year. But for those that have changed departments, if I include
everything, then they get counted multiple times in multiple
departments.


Do you mean May this year? That is, you are querying on a full date?

Then the SQL should basically do

SELECT employeeID,MAX(transferdate) FROM tbl_departmental_history GROUP
BY employeeID WHERE transferdate<may-this-year

(you figure out how to put a date in there, MS Access always puts me
into doubt; others may be more sure)

and then join this to the original table, linking employeeID and
date/maxdate to get at the last department.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.