Using MS Access/Enter "yes" or "no" in a table field based on another table
I apologize if this problem has been discussed in another thread. I have not been able to find it in this forum.
I have basic knowledge of Access and I am facing the following situation. I have to import 2 different Excel spreadsheets into Access. That's not a problem. One of them contains a list of employees and some details (including their ID number). So the fields for each register would be ID, Name, Last Name, DOB, Region, and so on. The other table is just a list of some of those employee's IDs. Not all the IDs are listed in this second table.
My problem is, I've been asked to add a new field in the first table, the one with the employee details, that has to show "yes" or "no", based on if that employee's ID appears in the second table or not.
Thank you very much for your help.
Its easy to do but I question the necessity of doing so. Access is a relational database and relational database design has a number of rules. These rules are called normalization. A search on that term will find many sources that explain it in greater detail then I can here.
One of those rules is not to have fields that are based on other data. Each finite should be based on on the table's key. So the way I would do this is with a query. After importing, open Query Design mode and add both tables. Join then on the EmployeeID. Next right click on the join line and select Join Properties. Select the join that says all from the main table and matching from the list table. Next add all the columns from the the main table. Finally, add a calculated column with an expression like this:
When you run this query You will see a column headed Included (you can use whatever heading you want) and either True or False if its in Table 2. You can format that column to display Yes or No if you prefer or even as a checked box that is checked and unchecked.
You can then use that query as the Recordsource of a form if you want, or use a similar expression in a control on a form:
=IIF(IsNull(DLookup("[EmployeeID]","Table2","[EmployeeID] = " & [EmployeeID])),False,True)
If this is a one time import and you will be getting rid of Table2 after you update Table1, that presents a different story. In that case, you use an UPDATE query. Again you add both tables to the query design. But after you have added the new field (a Yes/No datatype) to the table. Also do a full join where both match. Next add the new field only to the grid. Change the query to an Update query and in the Update row put True. When you run the query it will set the new field to True for those records in the second table.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA