Creating,Lookup,with,Microsoft computer Creating a Lookup with Microsoft Access Tables
----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc
Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-parent:"";mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin:0cm;mso-para-margin-bottom:.0001pt;mso-pagination:widow-orphan;font-size:10.0pt;font-family:"Times New Roman";mso-ansi-language:#0400;mso-fareast-language:#0400;mso-bidi-language:#0400;}1. Create a new table and save it as tblProducts. Create the following fields in this table:Field Name DataTypeProductID AutoNumberProductName TextProductPrice CurrencySet ProductID as the primary key2. Enter the following data into the tblProducts TableProduct ID ProductName ProductPrice1 A £5.992 B £10.593 C £21.994 D £35.495 E £19.99 Thats youfirst table done, you can now close it.3. The second table will contain information aboutdiscounts. The discount amount isdependant on the price of the product. For example if the product cost under £10 you only get a £2 discount. If its between £10 and £19.99 you get awhopping £5 discount too good to be true you might say! There are otherdiscount available too. Create a newtable and save it as tblDiscounts. Create the following fields in this table:Field Name DataTypeDiscountID AutoNumberStartPrice CurrencyEndPrice CurrencyDiscount Currency Set DiscountID as the primary keyEnter the following data into the tblDiscounts table:QuantityID StartPrice EndPrice Discount1 £0.00 £9.99 £22 £10.00 £19.99 £53 £20.00 £29.99 £84 £30.00 £39.99 £105 £40.00 £49.99 £12Now close thetableNearly there now, all we have to do is create a querythat includes both tables. Create a newquery in design view adding both the tblProducts table and the tblDiscountstable to the query. Now add the following fields to the query grid:From the tblProducts table add: ProductName& ProductPriceFrom the tblDiscounts table add: DiscountIn the criteria row for the ProductPrice field typethe following:Between [tblDiscounts].[StartPrice] And[tblDiscounts].[EndPrice]Save the query as qryDiscountLookup. Now run the query to see the appropriatediscount displayed for each product.You could calculate the new sale price if youliked. To do this switch back to designview in your query and in the next available field in your query grid write thefollowing in the Field: row.SalePrice: [ProductPrice]-[Discount]Before you run the query you had better format yournew calculated field to show the result in currency format. To do this click into the field on the querygrid then click View | Properties. Inthe Field properties window find the Format properties and choose Currency. Save and run query again. You shouldhave the discounted price for each product displayed in your new SalePricefield.
Creating,Lookup,with,Microsoft