Accounting Information Systems (excel+Access)
REMITTANCE BILL TO DATE YOUR ORDER # 10/21/12 100022 QTY ITEM 600 P1500 Joseph Franks SHIP TO 1717 Way Side Suite 3145 Bay City, Texas 77414 Tax Invoice # 105 Invoice Date 10/21/2012 Customer ID C100 SALES REP. F.O.B. SHIP VIA TERMS TAX ID Mark Fields UNITS Subtotal Please return the portion below with your payment. OUR ORDER # Franks Pool Service 2121 Ocean view Suite 303 Bay City, Texas 77414 Destination Standard Freight 02/10,n/30 212 DESCRIPTION DISCOUNT % TAXABLE UNIT PRICE TOTAL Pool Treatment 2% nontaxable 1.60 960.00 960.00 0.00 Shipping 25.00 Miscellaneous 985.00 BALANCE DUE 985.00 Invoice # 105 Customer ID C100 Date Amount Enclosed 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
