I need someone to help with Accounting problem that requires access and excel knowledge
REMITTANCE BILL TO DATE SHIP TO Joseph Franks 1717 Way Side Suite 3145 Bay City, Texas 77414 YOUR ORDER # 10/21/12 100022 QTY ITEM 600 P1500 OUR ORDER # Franks Pool Service 2121 Ocean view Suite 303 Bay City, Texas 77414 Invoice # 105 Invoice Date 10/21/2012 Customer ID C100 SALES REP. SHIP VIA TERMS TAX ID Standard Freight 02/10,n/30 212 TAXABLE UNIT PRICE TOTAL F.O.B. Mark Fields Destination UNITS DESCRIPTION DISCOUNT % Pool Treatment 2% nontaxabl e 1.60 Subtotal Tax Shipping 960.00 960.00 0.00 25.00 Miscellaneous Please return the portion below with your payment. Invoice # 105 Customer ID C100 Date Amount Enclosed 985.00 BALANCE DUE 985.00 1. Determine what table you need to create and what data elements to put in which table. I suggest you use the data normalization steps to develop a Third Normal Form. ( Show the normalization process in an Excel spreadsheet.) Customer No. begins with C. Refer to the invoice above. Add the following to your Access Database: Invoice 106: James Allen Allen Pool Repairs C200 San Antonio, Texas 115 Chavez ST. Zip code: 78201, Tel: (830) 234-4567, Fax: (830) 234 4568, Bought 12 (20 feet of Rope ) for $60 each 20 foot rope. P1018 10/1/2012 from Mark Fields, Freight paid by the buyer $100, Nontaxable, N/30, no discount, order no. 100023. Invoice 107: Lynn Jones Jones Specialty Pool Service C215 San Marcos, TX 2020 Railroad Drive, 78666, Tel: (830) 234-9876 Fax: (830) 234-4567 Bought Table and Umbrella (1 table, 1 umbrella) P1580 Table, P1581 Umbrella, Cost: Table $1000, Umbrella $300, Sales Person: Judy North, Freight: $200 (Delivered and assembled, paid by buyer), Nontaxable, 3/15, N/45, Order No. 100024. Date of Sale: 10/16/2012. Invoice 108: Alexander Brown Brown's Patio and Pool Supply C218, San Angelo, TX, 1820 Pecan Drive, 76902, Tel: (325) 567-2121, Fax (325)567-2122, Bought one Lounger P1585, Sales Person Ryan Green, Freight $50, (Paid by the buyer), Cost: $150,nontaxable, 1/10, N/15), Order No. 100025, Date of Sale 10/22/2012 James Allen Allen Pool Repairs C200 San Antonio, Texas 115 Chavez ST. Zip code: 78201, Tel: (830) 234-4567, Fax: (830) 234 4568, Bought Pool Treatment. P1500 10/23/2012 from Mark Fields, Freight paid by the buyer $100, Cost; 1.60 per unit, 400 units, Nontaxable, N/30, no discount, order no. 100026. 2. Build all the necessary table in Access database (Determine the Primary Key and the Foreign Key). Add five more sales to the tables. The last sale you add should be your name. After you complete building the tables, establish the relationships and establish referential integrity. Build Customer forms that includes all pertinent information about the customer, and a report. 3. Run a query that shows only the customers from San Antonio that bought Pool Treatment supplies and how much quality purchased. Save the Query as qryPooltreatmentSales. 4. Upon completion of the project. Save the Access database, compress, and upload to mid-term examination dropbox. Save the Excel Normalization spreadsheet and upload to the mid-term dropbox. Both documents will be saved in the same dropbox
