Access HW and I am lost on where to start.

[phifer2088]

I just need help getting going. I need to understand what is happening. I have pasted the assignment below.

1) Review the existing spreadsheet, and their sample data and make any assumptions you need about the company. Make any comments about the approach that you are going to follow, as you see fit.
2) Redesign the spreadsheet into a database with tables, add any fields that you think could be useful, even though it is not absolutely necessary. Explain your actions as you go along.
3) Perform a functional dependency analysis, and include it as part of your deliverables, for every step of the normalization process
4) Clearly take the existing un - normalized structure through distinct 1NF, 2NF and finally 3NF stages, using the shorthand representation. All three stages of normalization have to be clearly defined and depicted for full payment by the proprietor. Do not forget Primary keys assignment.
5) Finally, create an MS Access prototype (actual database), including the data provided, with the newly designed tables. Upload the prototype to the assignment area as part of the deliverables
6) Create a Query (In MS Access) that may include one or more tables, as needed, that provides the answer to the following request: List the Invoice number, Customer Name, and Parts Description for purchases by Customers that have an Account Balance of $3,000 or higher. Short them with the highest balance being displayed first (Descending values)
7) Create one Form: New Customer Input - this form should include any relevant fields that you think "Home Solutions Inc" might need (In MS Access)
8) Create One Report: Items Price List - this report should include any relevant fields that you think "Home Solutions Inc" might need (In MS Access)
· Make sure you document your work as you go along for full payment, as the owner of the business, wants to be able to show your work to new hires and they should be able to pick it up and understand why the database was put together the specific way you are suggesting.
· Below you can find the "database" that Joe has put together with his limited data management knowledge, and your starting point.

Invoice	CompanyName	Acct Balance	Acct Limit	Part1	Quantity1	Price1	Part2	Quantity2	Price2
87	Mary’s IT Services	5000	20000	Bolt	50	10	Nut	40	20
72	Mel’s Hotdogs	7200	7200	Bolt	100	10	Screw	45	30
57	Brookings	500	12000	Screw	60	30	Bolt	65	10
67	Parker’s	1000	5000	Nut	70	20
3	Action Computer’s	1000	10000	Screw	40	30
7	Kline’s	8000	9999	Bolt	30	10
9	John’s	10000	30000	Nail	25	50
17	Lee’s	700	1000	Bolt	75	10	Nail	120	50
19	Al’s	7000	7000	Screw	110	30
20	Al’s	7000	7000	Nut	90	20

You can assume that the quantities reflect packages and the prices are per item

Below you can find an alternative format for the same "database" that Joe has put together with his limited data management knowledge, and your starting point.

Invoice	CompanyName	Acct Balance	Acct Limit	Part	Quantity	Price
87	Mary’s IT Services	5000	20000	Bolt Nut	50 40	10 20
72	Mel’s Hotdogs	7200	7200	Bolt Screw	100 45	10 30
57	Brookings	500	12000	Screw Bolt	60 65	30 10
67	Parker’s	1000	5000	Nut	70	20
3	Action Computer’s	1000	10000	Screw	40	30
7	Kline’s	8000	9999	Bolt	30	10
9	John’s	10000	30000	Nail	25	50
17	Lee’s	700	1000	Bolt Nail	75 120	10 50
19	Al’s	7000	7000	Screw	110	30
20	Al’s	7000	7000	Nut	90	20

[orange]

What part of this isn't clear? What have you done so far? What is your plan in plain English?

[phifer2088]

Well right now everytime I make a change in Access it crashes so I am waiting until I can go into work early tomorrow to do more, but I have created 4 tables. One is the account information, customer information, stock, and orders. The account information will have a balance and limit. The Customer name has the company name and made up address, e-mail, and phone number. The inventory is strictly a list of items that are sold and their price. The order table will have invoice numbers, item sold, and qty.

[orange]

Build a data model of the things and how they relate to each other.
Create tables and relationships on paper.
Create some test data based on the instructions.
Identify some business rules/facts.
See if your model supports your test data and business rules.
Reconcile every anomaly.
Refine the data model and retest until the model supports the requirement.

Now, move to Access and using your model (that you know works) and build the database.
Access won't do anything that you haven't designed.

Good luck.

[phifer2088]

Finished and submitted. Thanks for the input.