asked on

Bring Back Job Position When Date Between Two Dates - MS Access Query

HI - I have two queries:

Query 1 - Has employee number and effective date

Query 2 - Has Begin and End Dates and Job Position

What Access function can I build to bring the Job Position together with the "effective date" in Query 1?

An example spreadsheet is attached.. Thanks.
EE.xlsx

John Tsioumpris

I believe that this DLookup will give you what you need

Expr1: DLookUp("Position";"Query2";"Beginning <= #" & [Date] & "# AND END >=#" & [Date] & "# AND Employee = " & [EmployeeID])

Open in new window

You should avoid the FieldName Date in Query1 because it will definitely cause issues

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

John,
Using domain functions in queries or code loops is extremely expensive. Each row in the resultset of the query or each iteration of the loop in code will cause a separate query to run for the domain function. So, if you have a thousand records, you are running a thousand queries. The join specified by Rey allows the database engine to do the heavy lifting and it is significantly more efficient. If you never work with any recordset larger than a few hundred rows, you can get away with it but beyond that, I would not recommend it.

Tom Farrar

ASKER

Thanks for all the comments. John, I was not able to get your DLookUp to work for me, and with further feedback from Pat thought it better to go with Rey's query (even though I technically ask for a Access function. Rey's query gave me the result I was hoping for. Thanks again to all of you. - Tom

Tom Farrar

ASKER

Thanks, Rey.