Lookup in a query

[Sammie]

I want to write an expression in a query to lookup a value in the Employees
table based on the field employeeID. This query is based on two tables:
[employees] and [vacationdays], which have a 1-many relationship.

When I enter a new record in the vacationdays table via the query, I enter
the employee from the employee table - so far so good. Then I want a field
which will automatically look up the startdate field value from the employees
table. I am just getting an empty field, as if it wants me to enter the
startdate again.

I wonder if this can only be done on a form? If so, I need some help with
the form also. The trouble I have is after looking up the first value
[employeeID], I can't get it to look up the second value [startdate]. I need
the [startdate] to be more than another column in the lookup field because it
is needed for other expressions.

 

[Access101]

I suppose these are a given:
EmpID in EMP_Table is tied to the foreign key EmpID in Vacation_Table
And that the relationship has referential integrity enforced.

I would create a Main from from Emp_Table, and a SubForm with
Vacation_Table, and create the Parent/Child field as EmpID

The StartDate would always show up on the EmpForm, and any time you enter
another Vacation in the subform, it will be related to the EmpForm.

Hope this helps.