Create a field for a changing Access data table using the Lookup Wizard

When information changes, it may be a challenge to ensure that the facts are consistent everywhere. With Access, you can make sure this is the case by using the Lookup Wizard to create a field for potentially shifting data.

In Access, your Orders form includes a Sales Tax field, which is set up as a list box that lets you choose from a list of states and the corresponding sales tax rates. When a state changes its tax rate, the accounting department notifies the IT department so the database administrator can make the change to the list. This process may be prone to error and expensive.

You would rather have the accounting department make the changes; however, you do not want the accounting personnel involved in database administration. Follow these steps to allow others to make changes based on new data:

1. Create a Sales Tax table that includes State and Sales Tax fields, which the accounting department will update as needed.

2. Open the Orders Table in Design mode.

3. Click the drop-down arrow of the Data Type cell for the Sales Tax field and select Lookup Wizard.

4. Click Next.

5. Click State ID in the Field list and then click the > button.

6. Click Sales Tax in the Field list and then click the > button.

7. Click State in the Field list and then click the > button.

8. Click Next and then click Finish.

9. Close the table and click Yes to save the changes.

You can now create a form that the accounting department can use to update the Sales Tax table. The Orders table lookup field derives its values from the Sales Tax table, so any changes made to the Sales Tax table will automatically appear in the Orders form.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.