Creating a Lookup with Microsoft Access

17 %
83 %
Information about Creating a Lookup with Microsoft Access
Science-Technology

Published on June 22, 2009

Author: chestert

Source: authorstream.com

Creating a Lookup with Microsoft Access Tables : www.bluepecan.co.uk Creating a Lookup with Microsoft Access Tables Microsoft Office Training Access Training The task at hand… : www.bluepecan.co.uk The task at hand… 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. The Products Table : www.bluepecan.co.uk The Products Table 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 The Products Table : www.bluepecan.co.uk The Products Table 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 Discounts Table : www.bluepecan.co.uk The Discounts Table 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. The Discounts Table : www.bluepecan.co.uk The Discounts Table 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 The Discounts Table : www.bluepecan.co.uk The Discounts Table 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 Create a Lookup Query : www.bluepecan.co.uk Create a Lookup Query 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. Create a Lookup Query : www.bluepecan.co.uk Create a Lookup 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] Create a Lookup Query : www.bluepecan.co.uk Create a Lookup Query Save the query as qryDiscountLookup. Now run the query to see the appropriate discount displayed for each product. Create a Lookup Query : www.bluepecan.co.uk Create a Lookup Query 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 . Create a Lookup Query : www.bluepecan.co.uk Create a Lookup Query Save and run query again. You should have the discounted price for each product displayed in your new SalePrice field. More Access Tutorials : www.bluepecan.co.uk More Access Tutorials For more Access Tutorials visit the Blue Pecan Free Microsoft Office Training page. For a direct link to this Access tutorial

Add a comment

Related presentations

Related pages

How to add lookup fields in a Microsoft Access table

How to add lookup fields in a Microsoft Access table ... In Microsoft Access, ... Use a wizard to create a lookup field. Start Access.
Read more

Create a lookup field - Access - support.office.com

Creating a lookup field in your Microsoft Office Access tables can help improve the efficiency of the data entry process for your database. A lookup field ...
Read more

Creating Multivalued Lookup Fields - Lynda.com

In Microsoft Access, you can use the lookup field to choose a value from a list instead of typing it in manually. Like the Default Values and Validation ...
Read more

Creating lookup fields - Lynda.com: Online Video Tutorials ...

Join Adam Wilbert for an in-depth discussion in this video Creating lookup fields, part of Access ... Access 2013 Microsoft ... creating a Lookup ...
Read more

Access 2010. Change field to being a lookup field ...

I also agree with John and Allen. I see ONLY two values in creating Lookup fields on the table level. The first if its a database that ONLY you ...
Read more

Lookup table in Microsoft ACCESS - Trigon Blue Inc.

Lookup Tables in Access. ... When creating the Company ... The property tells Microsoft Access which column values to use as the value of the ...
Read more

How to Create a Lookup Field in Microsoft Access 2007

This Microsoft Access tutorial walks you through the steps of creating a lookup field in Access 2007. You can use the Lookup Wizard to create ...
Read more

Microsoft Office Access 2013 Tutorial: Creating Lookup ...

Learn how to create a lookup form in this Microsoft Office Access 2013 ... 1.20 Creating Lookup ... Microsoft Access - Lookup Wizard ...
Read more

Create Lookup Field in a Table - YouTube

New Perspective Access 2010, Tutorial 05, page AC 254. Video walks through the steps on creating a lookup field in a table, including how to ...
Read more

Create an AutoLookup query in Microsoft Access - TechRepublic

Create an AutoLookup query in Microsoft Access. Microsoft Access allows you to base a form on an AutoLookup query ... Rather than creating the form from ...
Read more