Access Tutorial- Assignment
1.Create a new database in your area called YourNameSpecialtyGoods– eg if your name was Jack Spratt you would save your database as JackSprattSpecialtyGoods.
a)Import all the tables from the AccessTutorialAssignmentSourceData.
b)Create a new table called Orders that will be used to store data about orders by customers. The table should contain the following fields and properties:
InvoiceNo / Text / 4 / Unique Alpha-numeric Invoice number e.g. A123
ADate / Date/Time / Medium Date / Date of the invoice e.g. 09-Nov-01
Customer / Text / 50 / Customer’s name
ProductNo / Number / Long Integer / Product ID number
Quantity / Number / Long Integer / Number of items ordered
PercentDiscount / Number / Single / Percentage discount given on the order
Comment / Memo / Comments about the order e.g. special delivery instructions
c)Add an appropriate Primary Key to your table.
d)Create a relationship between the tables Orders and Products and Enforce Referential Integrity with Cascade Update and Cascade Delete
e)Save and close any open tables
2.Use the”Form Design” to create a Form called frmOrders for entry of data into the Orders table
a)In Design view adjust the form frmOrders to make it more “user friendly” i.e. change the formatting, labels, positioning of fields, etc to make the form look more visually appealing
b)Populate the table Orders with the following data
Field
/ Record 1 / Record 2InvoiceNo / U052 / A003
Date / 12/9/1994 / 10 Nov 2001
Customer / Richard Nixon / Mr J Howard
ProductNo / 7 / 16
Quantity / 8 / 3
PercentDiscount / 5 / 10
Comment / Leave behind the gate and please don’t get it wet / Please can I have a bigger discount than 10% next time
Field
/ Record 3 / Record 4InvoiceNo / M586 / N100
Date / 13/11/2001 / 1/4/2000
Customer / William Gates / Jack Horner
ProductNo / 5 / 19
Quantity / 200 / 10
PercentDiscount / 20 / 0
Comment / Look through the front window to see if anyone is home / Please leave the goods in the corner and keep your thumbs out of the goods
c)Save and close your form
3.
a)Use the Query Design view to create a query called qryOrders which shows the details of all the Orders for Products in the database. Use the following specifications:
- Field order:InvoiceNo, Customer, Date, Product Name, Quantity, UnitPrice, PercentDiscount, Net Price
- Calculated:Product Name = ProductID + ProductName [see if you can resolve the error, if you can’t then just put in the “ProductName”]
Net Price = Quantity * UnitPrice * (1 – PercentDiscount/ 100) - Sort order:InvoiceNo
b)Use the Query Design view to create a query called Overstocked that shows all Products where the UnitsInStock is greater than 80 OR the UnitsOnOrder is greater than 30. Use the following specifications:
- Field order:ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder
- Not VisibleUnitPrice
- Sort order:UnitsInStock, UnitsOnOrder
c)Use the Query Design view to create a query called NotAvailable that shows all Orders for Products where UnitsInStock equals 0 ANDDiscontinued is TRUE (i.e. Yes). [Discontinued will have to be added to the “Products” table. Use the following specifications:
- Field order:InvoiceNo, Customer, ProductName, UnitsInStock, Quantity, Discontinued
- Sort order:ProductName
d)Save and close your queries
4.
a)Use the AutoReport: Tabular wizard to create a Report called rptOrders. Base your report on the query qryOrders created in Question 3 above.
b)In Design view adjust the report rptOrders to make it more “user friendly” i.e. change the formatting, labels, positioning of fields, etc to make the report look more visually appealing
c)Save and close your database
a:\normal.dot20/04/19