.:CS 416 - Database Management:.
| MS Access 6 | ||
|
Create an update query to double the price of all products –
Create – Query Design, select Product Type table, OK.
Right-click the neutral - gray) query area, select query type –
update query (this will add the “update to” row). Select the
Price field and in the update to field built event that will
double the price in the Product Type table’s price field. (r.c.
– build – select table, price, *2). Name the query price_up.
UPDATE [Product Type] SET [Product Type].Price = [Product Type]![Price]*2; Create another update query that will change date hired fields in the Employees table to 02/02/2007 , name the query date_hired.
Create another update query to replace country in Customers table (Slovakia to Slovak Republic or SK to SVK) – Create – Query Design, table customers, query type = update; 1st field – country, update to e.g. SVK, criteria SK. Name the query country_update. Create a new – delete query that will remove all C category products (adjust your data entries in the table). Select the Product Type table, query type – delete. In the 1st column select *Product Type (table), Delete: From (this is the column where you choose a table, from which you want to delete some field(s)); in the 2nd column choose Product Category, Delete: Where, criteria = C. DELETE [Product Type].*, [Product Type].ProductCode FROM [Product Type] WHERE ((([Product Type].ProductCode)="T"));
Review
Open Project 2 (GardenCo. Database)
Create an update query to double the price of all products Name
the query PriceUp.
Create another update query to count the new, 15% discount price
for products for more than $50. Name the query Discount.
Create update query to change date hired fields in the Employees
table to 03/05/2006, name the query DateHired.
Create update queries to replace State codes in Suppliers table. Replace CA with California, NY – New York, NC – North Carolina, and MD – Maryland. Name the query CountryUpdate 1, 2, 3.
Create a new – delete query that will remove Canadian Customers from customers table. Create a delete query to remove orders made in February.
It is also possible to select, replace, search for desired elements in a TABLE, IN DATA ENTRY VIEW: Open the Exercise database.
In customer table, telephone number column, replace any 74 with 75 (select the phone# column, click Home – Replace; Find: 74, Replace with: 75, Look in: phone#, Match: Any part of Field. Find Next - Replace
Sort data in table by company name (A-Z) – select the company name column and click Home - AZ button. Then move the contact name column right behind the web page fields (select the column, drag and drop where desired), and country code (state column) behind the company name, and sort these two columns (company, country) Z-A (select both columns by holding down the Shift key)
In the product type table, use filter by form to display only products for e.g. $1.12 – select the Price column, click the Advanced - Filter by Form; in the price field available in the Filter table select 1.12 and click the Toggle Filter button. To view all the data, deselect the Toggle Filter button.
Use a filter to select all products except for those we have 50. Right-click in a quantity field of product, choose Number Filters – Does not equal…
Use filter by form to select products quantity = 45 and price = 20.80
Export the filtered data in MS Excel – click External Data – Excel; in the dialog window find desired directory, checkmark, Export data with formatting…, OK.
Use the FilterFor (r.c in any product code field) select only F products, Export the data, name the Excel file FProducts.
Export the whole customers table to MS Excel. (Not filtered)
In the Exercise database, design a new table for discontinued products.
Design an Append Query to add Product Names from Product Type table to discontinued table.
|
||