VlookUp or something?

[Jan T.]

Hi. I am using Access 2010 (accdb) and want to look up one of several
values where a couple criteria is met.
Here are two of the tables I use:

EmployeeID
FirstName
LastName
.....

HistoryID
EmployeeID
Department
FromDate

What I want is to look up wich Department the Employee worked in at a
certain date I choose.

Example:
Question: I want to see what department John Doe (EmployeeID 5)
worked in on the #2/25/2011#?

tlbHistory:
HistoryID EmployeeID Department FromDate
21 3 Accountance #9/1/2010#
22 5 Marketing
#11/3/2010#
23 6 Marketing
#11/3/2010#
24 5 Sales
#2/1/2011#
25 5 Support #3/1/2011#

Is there a lookup function or something, that can return what I want
in my form or report, what Department did John work in on the
#2/25/2011#?

Obviously, according to this table, it is easy to see that John was
working in "Sales" department.

Anybody have a function or suggestion here?

Thanks in advance!

Regards Jan T.
**************************************

 

[DeDBlanK]

Hi. I am using Access 2010 (accdb) and want to look up one of several
values where a couple criteria is met.
Here are two of the tables I use:

EmployeeID
FirstName
LastName
....

HistoryID
EmployeeID
Department
FromDate

What I want is to look up wich Department the Employee worked in at a
certain date I choose.

Example:
Question:  I want to see what department John Doe (EmployeeID 5)
worked in on the #2/25/2011#?

tlbHistory:
HistoryID     EmployeeID    Department       FromDate
21                3                      Accountance    #9/1/2010#
22                5                      Marketing
#11/3/2010#
23                6                      Marketing
#11/3/2010#
24                5                      Sales
#2/1/2011#
25                5                      Support            #3/1/2011#

Is there a lookup function or something, that can return what I want
in my form or report, what Department did John work in on  the
#2/25/2011#?

Obviously, according to this table, it is easy to see that John was
working in "Sales" department.

Anybody have a function or suggestion here?

Thanks in advance!

Regards Jan T.
**************************************

SELECT HistoryID, LastName & ", " & FirstName as Name, Department,
FromDate
FROM tblHistory, tblEmployee
WHERE FromDate = [Select Date] AND ([tblHistory].
[EmployeeID]=[tblEmployee].[EmployeeID]);
Should be close to get you started. In the query design, go to SQL
View and past it in there.