Database for SHONKY
DATABASE
SHONKY Cars would like to set up a database that would allow the sales staff quick and easy access to the following types of information:
- Which branches have a white Ford Falcon?
- Do we have any Mercedes below $30 000?
- What cars have a mileage less than 100 000 km?
To get this type of information about the cars in stock, you need to think about the list of fields that are required.
This should include:
Stock No
Make of car
Model of car
Year of registration
Registration number
Cost price
Sale price
Colour
Features
Branch
Now the required fields have been identified, you need to choose the data types you will enter into each field. There are 8 to choose from.
Text / Can contain alpha, alphanumeric or numeric data. The data cannot be calculated mathematically. Suitable for names, addresses, telephone numbers, postcodesMemo / If you want to enter a large amount of data (eg. Notes)
Number / Holds numbers only and used for calculations
Date/Time / Only for dates and times (eg birthdates)
Currency / For money values
Counter / A number that will automatically increase by one (eg for invoice numbers)
Yes/No / Used if the entry will be yes or no
OLE object / Used for pictures
Primary Key
Every table must have a Primary Key. This is a field that contains unique data that can be used to identify a specific record (employee ID or student number). In the SHONKY Cars database it would be the Stock No.
You are now going to design the CARS table for SHONKY Cars.
- Load MSAccess and choose Create a New Database using Blank Database. Click OK.
- You must give your new database a name before you start designing it. Call it SHONKY CARS. Save this into your student area in a folder called DATABASE EXERCISES.
- The Database Window appears, this window is the main control area for MSAccess.
- Choose Design View. Call the table CAR DETAILS
* You are now in Table Design and are ready to create the fields for your first table *
Click into the first row for Field Name and type Stock No (you are allowed to have spaces), then press Tab to move into Field Type. Click on the down arrow and choose AutoNumber, press Tab to move into Description. Description is optional. The Field Properties at the bottom of the screen are all correct, do not make any changes. AutoNumber is entered automatically and will increment by one for each record.
Click into the second row and continue to input the following details (you can choose the field type from the down arrow in the Field Type column). Click into the Field Size row of Field Properties at the bottom of the screen. When you are designing your database you must consider how wide the fields should be. For this exercise you are given this information. The field name Make will be 15.
Field Name / Field Type / Field SizeStock No / AutoNumber / Automatic
Make / Text / 15
Model / Text / 15
Year of Reg / Text / 4
Reg No / Text / 8
Cost Price / Currency / 6
Sale Price / Currency / 6
Colour / Text / 12
Features / Memo / Auto
Branch / Text / 10
The Memo field type will allow entry of word processing data. Up to a maximum of 65,535 characters (more than we will need!)
You now need to identify the Primary Key. Click into the Field Name for Stock No and click the yellow key on the toolbar. A key will appear to the left of the Stock No field.
Your table design should now look like the one below, check your details are all correct before you proceed.
Click on File, Save. Save your work into your multimedia folder as ‘Shonky Cars’
Now click on the View button on the toolbar to switch to the Datasheet view of your table
* You are now ready to input the data for the cars that are held by SHONKY Cars *
When you enter data it is easier to key in the information using the Tab key to move from field to field rather than clicking with the mouse (this is, of course, possible but is more time-consuming)
Enter the following data (remember Stock No will be automatically entered)
Make / Model / Year of Reg / Reg No / Cost Price / Sale Price / Colour / Extras / BranchFord / Falcon / 1988 / A 16 BD / 5500 / 6500 / White / Tow bar / Hobart
Ford / Laser / 1989 / B 63QT / 3500 / 4500 / Blue / Tinting / Hobart
Holden / Commodore / 1990 / A 99 DS / 5000 / 6500 / Red / CD / Devonport
Holden / Gemini / 1978 / A 87 AS / 2000 / 2500 / Green / Paint job / Launceston
So far you have been entering the data directly into the table. However, it is preferable if you enter data via a Form which you are now going to create using the Forms Wizard function.
Click on the Create tab, then click on the Form Wizard button
In the Form Wizard dialog box you need to select all the fields in the Available Fields pane. You can do this quickly by clicking the double arrow pointing to the right. All fields will then be moved into the Selected Fields pane.
Click Next, select Justified, click next.
The Forms Wizard will prompt you to name your form. Call it Car Details.
Select the option to Open the Form to View and Enter information. Click Finish.
* Your form should now be open and ready to input data. Maximise the screen*
Enter the following cars in your form. Remember the first field will be automatically entered. Use the tab key to move from field to field. As you move to a new record Access will automatically save your work.
Make / Model / Year of Reg / Reg No / Cost Price / Sale Price / Colour / Extras / BranchFord / Falcon / 1991 / A 32 QA / 8900 / 9900 / Blue / Mags / Hobart
Ford / Falcon / 1992 / B 64 TF / 9200 / 11000 / Silver / CD Stacker / Launceston
Toyota / Corolla / 1995 / A 45 RE / 10500 / 12200 / Yellow / Stripes / Hobart
Subaru / Forester / 1998 / A 39 DD / 23500 / 26000 / Black / 4x4, sunroof / Devonport
Toyota / Corona / 1992 / A 65 YZ / 6800 / 7500 / Silver / Low mileage / Hobart
Toyota / Celica / 1991 / B 87 GM / 8900 / 9600 / Silver / Alloys, ski racks / Devonport
Mazda / MR2 / 1998 / A 08 TT / 18000 / 20000 / Red / Paint protection / Hobart
Land Rover / Discovery / 1999 / B 74 EA / 29000 / 34000 / Green / 4 x 4, towbar, roof rack, CD, / Hobart
Mazda / Astina / 1997 / B 65 FR / 16500 / 17800 / Red / Stripes / Burnie
Toyota / Tarago / 1997 / A 39 YG / 25000 / 28000 / White / 8 seats / Burnie
Toyota / Corolla / 1997 / A EW 90 / 17000 / 18000 / White / Tinting / Launceston
Ford / Mondeo / 1997 / B 17 RU / 24000 / 27000 / Silver / CD with 10 CD stacker / Devonport
Subaru / WRX / 1999 / A 85 HY / 34000 / 39000 / Blue / Rear spoiler / Hobart
Nissan / Skyline GTS - R / 1993 / A 49 IJ / 15000 / 18000 / Silver / Body kit / Hobart
Mazda / RX7 / 1989 / B 05 PZ / 12000 / 15000 / White / Low profile tyres / Launceston
Mitsubishi / Lancer / 1999 / B 61 GA / 23000 / 26000 / Blue / CD and spoiler / Devonport
Mitsubishi / GT3000 / 1999 / B 54 JL / 40000 / 44000 / Red / Rally Art / Launceston
Suzuki / Swift / 1992 / A 33 TA / 75000 / 90000 / White / Stripes / Launceston
Honda / Civic / 1998 / B 00 AB / 21000 / 23000 / Green / Sunroof / Burnie
Honda / Legend / 1997 / B 62 HE / 75000 / 85000 / White / Leather seats / Launceston
Holden / Barina / 1995 / A 78 QT / 7000 / 8900 / Blue / Towbar / Hobart
Phew!! You have just experienced Data Entry – yes, it can be quite tedious and boring!
Enter another record for a car of your choice, ensure all the fields are completed.
Save your changes to the form, and close the form.
Go back to the table original table.
Click View, Datasheet view.
Print a copy of the Datasheet view of the table.
On reflection and when looking at this printout, the Sales Manager has decided that an extra field is required as some customers are unsure as to what type of car it is: Sedan, Wagon, Performance, People Mover, 4 x 4. You will now need to add an extra field to the Car Details Table.
Click View, Design view.
Select the Year of Reg field by clicking in the row selector to the far left. The entire row should be selected. Right click the mouse button and choose Insert Rows.
Key in the new field Type, which will be text and with a field size of 15.
Save the changes. Click View, Datasheet View
Enter the correct type for each car. If you are not sure – ask the class “car expert”.
QUERIES
In reality, the database would store hundred or even thousands of cars in all the branches of SHONKY Cars. You now have enough cars in your database to be able to ask questions of it. When we ask questions we are querying the database.
Query 1
The manager has asked you to print a list of every car held by every branch in the company.
Click on the Create tab, then click on the Query Design button
You are requested to Add a table that MSAccess will get the information from. For our database we only have one table at this stage.
Select the CARDETAILS table, click Add and then Close.
You now need to add fields that you would like in the Query.
Double click each field in the Car Details table box. They will be copied to the query below.
To see the results of your Query click on the Run button in the Toolbar (it looks like an Exclamation Mark)
It would be preferable for all fields to print on one page. If it is too large you can reduce the width of the fields by either clicking and dragging or double clicking each field divider.
The results are in order of Stock No which is not what we want for the purpose of this query, return to the Design of your Query by clicking the Design View button on the ribbon.
Click into the Sort row of your Query under Make, click the down arrow and choose Ascending. Now click into the Sort row for Model, click the down arrow and choose Ascending.
Click Run, your query will now be sorted by Make and then by Model.
Save the Query as Total Stock
Query 2
The Managing Director of SHONKY Cars is concerned that the Annerley branch has too much stock on hand. He will need a query creating that shows the stock for this branch.
Create a new Query, add the Car Details table.
You need all fields except Features so add each one to the design of the query.
Underneath the Branch Field, click into the Criteria row. Type in Hobart. You will notice that it changes to “Hobart”.
Save the query as HOBART
Click Run
Query 3
The last query (Query 2) was so successful that the Launceston branch would like a similar query for them.
Create a query for Launceston but they would like it sorted into Make and then Model. Save it as LAUNCESTON SORTED. Print a copy.
Query 4
A customer has enquired whether SHONKY Cars has any Ford Falcon’s in stock. She is not worried about price, colour or which branch it is at.
The manager has asked you to create a query to find out this information. He does not want the cost price displayed.
Create a new query. It will be saved as FORD FALCON.
Choose every field except Cost Price and Stock No.
In the Criteria row type Ford under Make and Falcon under Model. They must be on the same Criteria row.
Query 5
The staff at SHONKY Cars are really starting to see how powerful MSAccess is and how useful it can be for them when customers ask for specific requests. A customer has now asked for any Toyota car that is less than $22000.
Create a new Query called TOYOTA<22000
Use every field except Stock No and Cost Price (remember we don’t want to show our customers how much profit we are making on a car!).
In the criteria row enter Toyota in Make and <22000 in Sales Price. Run the query.
Query 6
Tom at Burnie just phoned and he wants to know if we have a Subaru WRX anywhere. Create the query and save it as SUBARU WRX. As this is for a member of staff you can show cost price (it helps him with the negotiating).
Query 7
Jill at Devonport wants a query to see if we have a Honda Legend anywhere. Show all fields. Save as HONDA LEGEND..
Query 8
A customer has enquired if we have any white cars in stock, she feels that white cars are much safer. Create a query called WHITE CARS. Don’t show Stock No, or Cost Price. Sort by Make and then by Model. Print a copy.
Query 9
Another customer has an aversion to white cars. She would like to know what cars we have in any other car apart from white. Save as NOT WHITE CARS. Choose the appropriate fields for a customer. In the Criteria row type Not White. Run the query. Sort by Make and Model.
Query 10
The customer from Query 8 WHITE CARS now thinks that red cars are good for the image. Create a new query called RED CARS. Use the necessary fields for a customer. Run the query. She might change her mind again.
Query 11
The customer from Query 8 WHITE CARS wants to change her mind again (no comment!). She is only interested in cars that are white and <$20000. Open the White Cars query in design view and add an extra criteria under the price of <20000. It needs to be on the same criteria row as white. Run the query.
REPORTS
The queries you have printed so far can be printed in a much more professional format by using a Report.
Reports are available in various formats and can be based on a table or a query.
You will use the Report Wizard to start and modify the report later if necessary.
Report 1
The Managing Director has asked for a full report of all cars at all branches for his Accountant.
Click on the Create tab, then click on the Report Wizard button
Click on the down arrow next to “choose the table or query….” Select Car Details table. Click OK.
Choose all the fields except Features. Click Next.
Don’t add any grouping at this stage.
Click Next. Add Sorting for Make and then Model.
Click Next. Choose Tabular and Landscape, then click Next and Finish.
You may need to do some fine-tuning in Design View to make all your fields fit on the page. Ask your teacher for some help with this if necessary.
Save the report as Car Details.
Report 2
Create a report based on the Hobart query. Choose all fields except features. Save it as Hobart report.
Report 3
Create a report based on the WHITE CARS AND <20000. Save it as REPORT WHITE CARS AND <20000.
Report 4
Now the Managing Director wants a full report of all branches.
Create a new Report based on the Car Details table. You will need the following fields:
- Branch
- Make
- Model
- Year
- Cost Price
Follow the Report Wizard through and when it asks for Grouping choose by Branch. Then sort by Make and Model - Sales are going up as a result of the database. Well done!!
CHANGES TO THE DATABASE
The branch managers want their branch to be always entered in capitals. As there are various employees who enter data it is easier just to enter this in the database design area.
Click on the Car Details Table, then click on Design View.
Click on the Branch field and then click down into the Field Properties, Format. Type in a >. This will mean the data in this field will always be displayed as capitals.
The Managing Director has just had a meeting with all the Branch Managers. The database is extremely successful but they have decided it would be extremely useful to have the mileage of cars recorded in the database as many customers ask this question.
As you are now the Access Database Expert you have the job to amend the design of the CAR DETAILS table.
You will need to go back into the Design View of the Car Details table and insert an extra row before Year of Reg. It will be called mileage and will be a Number field. Its property will stay as Long Integer.
That was the easy bit!! Now you have to go to Datasheet View and enter the Mileage for all the cars in stock. Fortunately they have been provided in stock number order.
Stock / Mileage / Stock / Mileage1 / 120000 / 12 / 15000
2 / 100500 / 13 / 10000
3 / 65000 / 14 / 94000
4 / 32000 / 15 / 67000
5 / 160000 / 16 / 20000
6 / 98000 / 17 / 30000
7 / 32000 / 18 / 79000
8 / 68000 / 19 / 32000
9 / 78000 / 20 / 45000
10 / 32000 / 21 / 35000
11 / 45000 / Your car / ?
The manager of Devonport has phoned and said that now that you have entered the extra data in the mileage field could he have a report of the Devonport Query. He does not need the Features or Colour fields.
Open the Devonport query and add the mileage field to it.
He likes the idea of having it sorted by Make and Model so add this to the Sort Row.
If you run this query you will see that it shows Devonport for every record. This is unnecessary but you need to have the field in the query. Uncheck the tick in the Show Box.
Your query will now use the Branch field but will not show it in the query.
If your query goes over to a second page remove the Year of Reg field.