Ja************@ge.com wrote:
of a way to do this? Is there some sort of append query that can be
run to find entries in one table matching the entries of another table
as the criterion? Any help would be much appreciated!
You can use a subquery that utilizes criteria to obtain the foreign
key. Here's an example:
Let's say I have tblEmployees that is mostly normalized and
tblVacations that uses a single field for the employee name from an
imported spreadsheet.
tblEmployees
EID Autonumber
EFirstName Text
ELastName Text
EID EFirstName ELastName
1 Joe Young
2 King Kong
3 The Hulk
tblVacations
VacationID Autonumber
VacationName Text
StartDate Date/Time
EndDate Date/Time
VacationID VacationName StartDate EndDate
1 King Kong 6/8/06 6/13/06
2 Joe Young 7/10/06 7/14/06
qryOnVacation:
SELECT EFirstName, ELastName, (SELECT VacationID FROM tblVacations
WHERE Date() BETWEEN StartDate AND EndDate AND VacationName =
tblEmployees.EFirstName & ' ' & tblEmployees.ELastName) AS VacationKey
FROM tblEmployees;
!qryOnVacation:
EFirstName ELastName VacationKey
Joe Young Null
King Kong 1
The Hulk Null
That was a warm up. Now I want to use the names in tblEmployee to
eliminate tblVacations.VacationName.
qryNewtblVacation:
SELECT VacationID, StartDate, EndDate, (SELECT EID FROM tblEmployees
WHERE EFirstName & ' ' & ELastName = tblVacations.VacationName) AS EID
FROM tblVacations;
!qryNewtblVacation:
VacationID StartDate EndDate EID
1 6/8/06 6/13/06 2
2 7/10/06 7/14/06 1
It's not a perfect example but should give you an idea of a way to
start matching up the ID's. The DLookup function acts much like a
subquery so it could be used instead the subquery.
James A. Fortune
CD********@FortuneJames.com