Music Store Database => DUE: April 30th
Create a database named "My Music Store" with three tables.
Create "meaningful" relationships between the three tables.
(Each table should have a primary key and a foreign key).
Each table should have at least five fields (although you may have more than five fields)...
Make the customers real, fictitious or famous names. Use an input mask on at least one field.
Make the guitars prices range anywhere from $500 to $10,000 each
with at least one priced under $1000 and at least one priced over $2000.
Use a lookup field for at least one column.
Adjust the column widths in all of the tables so all of the data is visible/readable.
Populate each table with 5 records. For example you create 5 real or fictitious guitars, customers, and sales....
Create a Form for each Table.
Then create a horizontal tab Navigation Form.
Next, create a query named "Premium Guitars" that displays Guitars priced over $2000, be sure to display at least three fields in this query...
BE SURE AT LEAST ONE OF YOUR PRODUCTS IS PRICED OVER $2000.
Create a second query using whichever table keeps track of purchases. Use no more than six fields, but use at least three fields for product name, transaction date, and Item cost.
Include a parameter prompt asking "Which product" in the product field.
Also, add additional parameter prompts for both a start and an end date in the Date field.
(Be sure that when the start date is 01/01/17 and the end date is 12/31/17 that at least one product purchase falls within this range).
Name the Query: "Purchase and Date Range".
Create a third query from your purchases/sales table. Have this query display the average number of items sold that cost greater than $300.00.
Format the Product Cost field to display as currency to 2 decimal places and your newly added field to display as fixed to 2 decimal places.
BE SURE THAT AT LEAST ONE OF YOUR PRODUCT PRICES IS GREATER THAN $300.00.
Name the Query: "Average No Items over 300"
Create a fourth Query from your products/guitars table.
Use at least the product name and price fields to begin with.
Next, add a calculated field to calculate what a 6% price increase would be. Format this new calculated field column to display as Currency to two decimal places.
Save your query as 6% price increase.
Create a report from your Premium Guitars Query, name it: PGR.
Create a report that contains all guitars and who sold them, name it: GuitarSales. Group by Sales Person and sort by guitars in ascending order.
Change the database settings to compact on close.
Save all your work and upload your file.