Unit 4 Assignment | Complete Solution

Unit 4 Assignment

    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.
    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
Create a table named Inventory using the instructions above. Use the data shown below, with the following guidelines:

    Set up an auto number to use for a primary key.
    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
    
    
Create a one-to-many relationship between SALES_REP and INVENTORY. Create a form to enter and edit inventory information. You decide on the design of the form. 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
    
    
The Chevrolet Lumina was sold on 5/20/08 by Bauer for $9,300. Update the record using the form. 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.

    All vehicles available for sale with a selling price less than $6,000, sorted by cost in descending order.
    All vehicles sold, grouped by Sales Rep.
    All vehicles made by Ford and Toyota.