CIS223 Unit 4 Assignment | Complete Solution

Unit 4 Assignment

1.    You have been asked by the owner of a pre-owned car dealership to set up a way to track the cars in his inventory. He often needs to search his records for specific makes and models or cars made in certain years. He also wants to be able to generate reports based on his sales and reports on which sales representatives are doing the most business.
2.    Create a new Access database and set up a table named Sales Rep. Be sure to create attributes, set a primary key, select data types, and add descriptions as necessary. Use the following information:
Rep Number    Last Name    First Name    Address    City    State    ZIP    Phone
1    Perry    Jim    3453 E. State    Jacksonville    FL    32231    904-555-4353
2    Bauer    Evan    5347 W. Grand    Jacksonville    FL    32232    904-555-3423
3    Dickens    Billy    2351 S. Ferguson    Jacksonville    FL    32232    904-555-5632
3.    Create a table named Inventory using the instructions above. Use the data shown below, with the following guidelines:
a.    Set up an auto number to use for a primary key.
b.    Use a drop-down list to enter the condition. Possible conditions are Excellent, Good, Fair, and Poor.
Make    Model    Yr    Description    Condition    Cost    Selling Price    Date Arrived    Date Sold    Sales Rep
Pontiac    Grand Am    2005    4-Door, Red    Excellent    $8,000    $9,990    5/5/08    6/1/08    Perry
Lincoln    Town Car    2001    2-Door, White    Good    $5,500    $5,995    4/15/08    4/20/08    Dickens
Chevrolet    Cavalier    2005    4-Door, Blue    Excellent    $7,000        5/15/08        
Toyota    Corolla    2001    4-Door, Black    Fair    $4,000    $4,500    5/1/08        
Ford    Tempo    2002    2-Door, Red    Poor    $2,000    $2,300    5/5/08        
Chevrolet    Lumina    2005    2-Door, White    Excellent    $8,500        5/12/08        
Ford    Focus    2003    5 Speed, Black    Good    $6,500    $7,000    4/20/08    4/30/08    Perry
Ford    Escort    2000    2-Door, White    Excellent    $5,500        5/3/08        
Plymouth    Neon    2001    4-Door, Blue    Good    $6,500        5/1/08        
4.    Create a one-to-many relationship between SALES_REP and INVENTORY.
5.    Create a form to enter and edit inventory information. You decide on the design of the form.
6.    Enter the following record into the Inventory table using the new form:
Make    Model    Year    Description    Condition    Cost    Selling Price    Date Arrived    Date Sold    Sales Rep
Ford    Taurus LX    2003    Wagon, Gray    Excellent    $8,200        5/20/07        
7.    The Chevrolet Lumina was sold on 5/20/08 by Bauer for $9,300. Update the record using the form.
8.    Create the following reports using whatever method you prefer. Format the reports so that information is easy to find, give each report an applicable title, and include the date and your name on the report. Hint: You need to create a query to obtain the desired information, and then base the report on the query.
a.    All vehicles available for sale with a selling price less than $6,000, sorted by cost in descending order.
b.    All vehicles sold, grouped by Sales Rep.
c.    All vehicles made by Ford and Toyota.