Something similar to a LOOKUP in excel

[jppevy]

I have two tables in my db. The primary ID in both tables in the employee ID. The first table is standard information of all employees. The second table has only has one column of employee ids that have taken a vacation this year. Of the hundreds of employees listed in the first table, only a handful have; therefore, on the second table there are about 40 employee ids.



My task: I want to create a new column in the first table called vacation. Then, if there is an employee that has taken a vacation--their id would be listed in the second table--the column would fill with the word "Taken." If they have not taken a vacation, then it would fill with "not yet."

I know how to do this in excel with nested a isnumber and match function, that looks this up automatically, but am confused about what is the most efficient way to do this in access.

Thanks so much everyone.

[pbaldy]

Generally speaking you shouldn't store the value in the main table, as it's a calculated field. Do you empty the second table out at the beginning of each year? Not how I'd go about it, but you can get the look you want in a query by including both tables. Edit the join so you get all records from the main table.

[ssanfu]

The primary ID in both tables in the employee ID

Also, this means that you have a one to one relationship between the tables. Not a normal set up.

I'm just saying........

[jppevy]

Hey thanks guys for such quick responses. This is just a one-off thing and thats how the db came to me. Its just these two tables.

I can run the query with the modified join, but I don't know how to have it automatically fill-in either "taken" or "not yet." For instance, in the query the last column shows the IDs of those from the second table only on the rows for when it matches. So that part works. But, i am wondering if there is any way to have it write in something, or do I just have to do it manually?

Thanks again.

[pbaldy]

This type of thing:

Status: IIf(IsNull(SecondTable.ID), "not yet", "taken")