How to Create and Use a Look-up Table in Microsoft Access

Eidolonai March 11, 2018


Create a new table and save it as tblProducts. Create the following fields in this table:

Field Name   Data Type

ProductID   AutoNumber

ProductName Text

ProductPrice   Currency


Set ProductID as the primary key


Enter the following data into the tblProducts Table

Product ID Product Name ProductPrice

1   A   5.99 euros

2   B 10.59 euros

3   C 21.99 euros

4   D 35.49 euros

5   E 19.99 euros

That’s you first table done, you can now close it.



The second table will contain information about discounts. The discount amount is dependent on the price of the product. For example if the product cost under 10 euros you only get a 2 euro discount. If it’s between 10 euros and 19.99 euros you get a whopping 5 euro discount – too good to be true you might say! There are other discount available too.

Create a new table and save it as tblDiscounts. Create the following fields in this table:

Field Name Data Type

DiscountID AutoNumber

StartPrice Currency

EndPrice Currency

Discount Currency

Set DiscountID as the primary key


Enter the following data into the tblDiscounts table:

QuantityID   StartPrice EndPrice Discount

1 0.00 euros 9.99 euros          2 euros

2 10.00 euros 19.99 euros 5 euros

3 20.00 euros  29.99 euros   8 euros

4 30.00 euros  39.99 euros 10 euros

5 40.00 euros  49.99 euros   12 euros

Now close the table



Nearly there now, all we have to do is create a query that includes both tables. Create a new query in design view adding both the tblProducts table and the tblDiscounts table to the query.

Now add the following fields to the query grid:

From the tblProducts table add: ProductName & ProductPrice

From the tblDiscounts table add: Discount

In the criteria row for the ProductPrice field type the following:

Between [tblDiscounts].[StartPrice] And [tblDiscounts].[EndPrice]

Save the query as qryDiscountLookup. Now run the query to see the appropriate discount displayed for each product.

You could calculate the new sale price if you liked. To do this switch back to design view in your query and in the next available field in your query grid write the following in the Field: row.

SalePrice: [ProductPrice]-[Discount]

Before you run the query you had better format your new calculated field to show the result in currency format. To do this click into the field on the query grid then click View | Properties. In the Field properties window find the Format properties and choose Currency . Save and run query again. You should have the discounted price for each product displayed in your new SalePrice field.

Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Access training courses as well as other Microsoft Office training options.


Copyright © Unique Coffee Tables 2018