Free Microsoft Access Tutorial - Creating a Lookup with an Access Query

Microsoft Office Training > Access Training > Free Computer Training > Creating a Lookup in Microsoft Access Query

Please feel free to use this content on your web site, but please include the links at the end

Creating a Lookup with a Microsoft

Access Query

In this example we will lookup values in one table based on values in another.  You may be familiar with the lookup concept if in Excel you have used VLookup or HLookup functions. I am going to use a really simple example to get you started.   

1. 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

2. Enter the following data into the tblProducts Table

Product ID	Product Name	ProductPrice
1	A	£5.99
2	B	£10.59
3	C	£21.99
4	D	£35.49
5	E	£19.99

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

The second table will contain information about discounts.  The discount amount is dependant on the price of the product.  For example if the product cost under £10 you only get a £2 discount.  If it’s between £10 and £19.99 you get a whopping £5 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	£9.99	£2
2	£10.00	£19.99	£5
3	£20.00	£29.99	£8
4	£30.00	£39.99	£10
5	£40.00	£49.99	£12

Now close the table

3. 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.
   

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Access Training.