Assignment Instructions:
Create a Database
Start Access and create a Blank database, naming it "Inventory."
On the Home tab, click View and then name the new table "Inventory."
In Design view, change the field name ID to Item ID and verify that it's data type AutoNumber and has been selected as the primary key.
In Design view, add the remaining fields and corresponding data types:
|
Field Name
|
Data Type
|
Description
|
|
Item Name
|
Short Text
|
|
|
Category
|
Short Text
|
Appliances, Electronics, Jewelry, Other
|
|
Manufacturer
|
Short Text
|
|
|
Model
|
Short Text
|
|
|
Serial Number
|
Short Text
|
|
|
Purchase Date
|
Date/Time (Short Date)
|
|
|
Purchase Price
|
Currency
|
|
|
Merchant ID
|
Number
|
|
|
Online Purchase
|
Yes/No
|
|
|
Credit Card Purchase
|
Yes/No
|
|
|
Warranty Type
|
Short Text
|
Store, Manufacturer, Other
|
|
Warranty Length
|
Short Text
|
|
|
Repair
|
Yes/No
|
|
|
Repair Date
|
Date/Time (Short Date)
|
|
|
Comments
|
Short Text
|
|
Save and then close the Inventory table.
Create a second table in Design view. Name the table "Merchants."
Add the following fields and corresponding data types. Be sure Merchant ID is the primary key:
|
Field Name
|
Data Type
|
Description
|
|
Merchant ID
|
AutoNumber
|
|
|
Merchant Name
|
Short Text
|
|
|
Address
|
Short Text
|
|
|
City
|
Short Text
|
|
|
State
|
Short Text
|
|
|
Zip
|
Short Text
|
|
|
Email
|
Hyperlink
|
|
|
Website
|
Hyperlink
|
|
|
Telephone
|
Short Text
|
|
|
Fax
|
Short Text
|
|
Save and then close the Merchants table.
Create a relationship by linking the Merchant ID in the Inventory table to the Merchant ID in the Merchants table. Be sure to enforce referential integrity.
Create Forms and Populate the Database
Create a Merchants form that looks similar to the figure attached.
Create an Inventory form that looks similar to the figure attached.
Use the Merchants form to populate the appropriate sections in the Merchants table with the records listed attached.
Use the Inventory form to populate the appropriate sections in the Inventory table with the records listed attached.
Query the Database
Create a select query that retrieves the Item Name, Purchase Date, Purchase Price, and Online Purchase fields from the Inventory table for items purchased online.
Have the select query sort the results in chronological order by purchase date.
Be sure to format the Datasheet view so that all field names and data are displayed entirely.
Save the query, naming it "Online Purchases," and then close the query.
Create a select query that retrieves the Merchant Name from the Merchants table and the Item Name, Category, Manufacturer, and Purchase Date from the Inventory table for appliances.
Have the select query sort the results in alphabetical order by merchant name.
Be sure to format the Datasheet view so that all field names and data are displayed entirely.
Save the query, naming it "Appliance Purchases," and then close the query.
Create Reports
Create a tabular report using the Online Purchases query.
Sort the report data by purchase date.
Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page, as shown in the figure(attached).
Save the report, naming it "Online Purchases," and then close the report.
Create a tabular report using the Appliance Purchases query.
Reduce field widths so that all fields are displayed in portrait orientation.
Move the Page 1 of 1 footer so that it's centered attached the report data.
Delete the record count and the summary line attached the Merchant Name data.
Group the report data by merchant name.
Sort the report data by purchase date.
Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page, as shown in the figure.
Save the report, naming it "Appliance Purchases," and then close the report.
Attachment:- Database management system.rar