The customer would like to be able to find out what sort of cars are available for him or her to buy, for example if they have £10,000 to spend, the salesman would perform a search for all the cars costing £10,000 and less. Or they could perform a search to find all the cars that cost £10,00 and above.
Using Microsoft Access is much harder than using the other type of programs, this is because it has a different type of database than other programs that you can use to create databases. In programs such as Microsoft Excel you can only create a flat database, in a flat database you cannot create two or more tables and link them together. For this project, I need to be able to link the tables together, so that I can perform searches easily. I can create another table, which would contain all the garages, and it would sort the cars by which cars where in which garage.
I will now go through what we had to do. We had had to create a table containing information on 50 cars. This table would contain all the information a first time buyer would require to buy a car. One this table had been completed we needed to create another table which would contain all the information about the garages, one this was done we would like the two tables together, now that the tables are linked together we would be able to perform searches that would allow us to find a specific car in the database which would suite the buyers needs. It would also help the salesperson find the car that was well suited to the buyer much quicker. One the two tables had been linked together we needed to create a query, the form would allow us to do simple searches, to find information about the cars like “make”, “model”, “price” etc.
Once the above tasks have been preformed, we need to create forms and queries. These are all used so we can find out information about the cars easily and without much effort, and very quickly. If you wanted to find all the cars under £10,000 you would need to click on the max price query and type in £10,000, a list of cars up £10,000 in price will all be listen. This is what we had to do, once this was all done. We had to make a report and go through what we all did in detail.
Evaluation for a car owner
On a scale of 1-5 how easy would you say the car table was to use and understand?
1 2 3 4 5
Would you use this system if it were set up on a web site, to find a new car?
Yes No
Did the table contain all the information about the car you would want to know?
Yes No
What would you change about the system? If anything?
Would it be better if they were more exotic cars?
Yes No
Thank you for taking a few minutes out of your time to fill in the questionnaire.
How I Created My Microsoft Access
Database
Firstly I will explain what this software can be used for.
- Designing and creating a relational database
- Creating databases which can be linked
First of all we will be designing a database, this what the first database should look like by the time you have finished, here’s one I made earlier:
Before you get to this stage you will need to find information on 50 cars, the information I have chosen to add on my database is, Make, Model, Year, Mileage, transmission, Power Steering, Price, Colour, Fuel, Number Of. I have chose to use this information because I believe it is the most important information. Doors, Garage One this information has been entered another table must be created, the second table must contain the information about the garages, the information in this table contains should be, garage name, owner, address, phone number and name of contact. It should look something like this:
The name of the garages must be displayed exactly the same as in the table containing the information about the cars. The information about the cars should be something like this, Model, Make, Year, Colour, Number of doors, Price, Fuel and other basic information about the car which a first time buyer would want to know.
How to create the first table:
Once Microsoft Access has loaded it should look something like this:
Click on the “Database” then chose a name for your file and press “Save” after you have done this a screen like this should appear once you have chosen the name for your database.
Click on “Create New Table Using Design View”, on the screen that has just appeared, which should like something like this:
Where it says “Field Name” enter all the fields you want to appear in your car database. The different fields you enter should all the information you think a first time buyer would like to know. Once all of the information has been entered save your work then open up your table and enter the information about 50 cars, for example enter what type of fuel the car uses, the price, make, model, year of manufacture, colour of car, which garage you can buy the car from also the extras the car has. Once this has been achieved you must save and exit, you should then come up with the screen mentioned above, click on “create new table in design view”. Once it has loaded up enter the name of all the garages, the address, the phone number of the garages and a contact number for the garages. Make sure the name of the garages is exactly the same as the name in the other table containing all the information about the cars. If you made a spelling mistake when entering the names of the garages when you come to linking both tables together it will not work, so you must be extra careful at this stage because it could waste lots of your time if you make one mistake. One all the information from the garage table has been entered accurately, save the table and close down the table. Once the table has been closed down a screen like this should appear:
Right click on the empty white space (where the arrow is pointed) and select “Relationships...” When it has been clicked on another screen should appear. In this screen you need to select which tables you want to link together, is my case I needed to select the “Garage” and the “Cars” table to link together. Once this has been selected, you need to decide which parts of each table you would like to relate to one another. Relate the “one to the many”. Once the tables have both been linked to each other it should look something like this:
To test if the tables have both been linked properly go into the garage table and click on one of the garages, a list of all the cars within that garage should appear.
Queries
Queries are used to search for s specific car, or a specific feature of a car. If you wanted a car with air conditioning in the query box, it would come up with a lost of all the cars with air conditioning.
To create a query click on query in the object list.
Once you have clicked on the query, click on create query in design view. From here just follow the instructions and then enter what you are looking for and the database will come up with a list of cars matching your query.
Reports
To create a report click on “report” towards the left hand side.
Then click on “create report by using wizard”, a new screen should load up where you enter the fields you might want to include in the report. A report summarises the cars that you have in your garage, it puts all the information into a neat table. The information that it summarises are the ones that you have clicked on when designing the table. The finished table might look something like this.
As you can see I only have 3 fields in my reports, other fields can be added very easily when making the report in design view. All you have to do is highlight the other fields that you require and click here:
Testing
After completing the designing of the solution according to the assignment proposal, I moved forward to test the final outcome, while inspecting if the database as whole functions as expected. The table below shows the numerous areas in which I have checked, detailing the results of the testing, a suitable corrective action for the problem (if and is required) and evidence of this inspection using screenshots and printouts.
Instructions on how to use my database and perform basic searches
There are many things that you can do with my database, you can easily search for cars. And perform specific searches with ease. In my database I have made it so you can search for the following things with ease.
- Which garage the car comes from
- Model of car
- Minimum price of car
- Maximum price of car
- Maximum number of doors
- Make of car
To perform a search for all the cars from specific garages follow the following instructions:
- Presuming that you are using a desktop PC. Open up Microsoft Access by double clicking on the icon. Labelled “MS Access”
- Open the required database, to this you should place your hand on the mouse and point the cursor on the section named “File”, then left click. Move the cursor down to open and left click on it. A screen will then appear. Here you will see the file name you are looking for it should be named “Garage Project”, double click on it. (You should come to a screen that looks something like this):
- Then click on the “queries button”
Then you should come to a screen that looks something like this:
From here you can see what sort of queries are available for you so search through, as you can see the ones I have are “Garage”, from here you can find all the cars from a specific garage. I also have model, minimum price, maximum price, maximum number of doors and the make of the are.
Find all the cars below a certain price:
To find all of the cars below a certain price follow the following instructions
- Follow the instructions above, stop once you have reached the screen where you can use the queries.
- Double click on the minimum price box, you should then reach a screen that looks something like this:
In the box that came up, enter in the minimum price. Then press “ok”.
If you entered £15,000 as the minimum price all the cars above that price will appear in a screen such as this:
To perform other searches just click on the required search field, and follow the instructions as above
Analysis
Mark's Masters is a small family run business that has decided to expand, and make use of new technology available. It employs five salesmen and two phone secretaries. Because of the lack of computers in this business, it is extremely unorganised. It is very hard to find out information about a certain car and when the will be getting a new delivery of the cars in. Because it has now decided to expand and invest in new computer technology, I will install a complete new computer system. The new computer system will organise all of the cars automatically, all you will have to do is enter the information of each car into the database, the database then can sort each car by data added, manufacturer, price etc. The new system will allow customers to do a simple search for cars which are in their price range, are the make they want, have the accessories they want etc.
The new computer system will allow much more work to be done because the employees will not have to spend a lot of time searching through car details, to find the car that the customer is looking for. Instead there will be 2 or 3 ways of searching for a car depending on what type of car you are looking for. You can do a search for all cars below a specific price, or all of the cars above a specific price. Here is the exact list of ways for which you can search for cars.
Max price,
Minimum number of doors,
Minimum price,
Maximum number of doors,
Make,
Model,
Garage location.
The reason I made it so you could search the database for the above list is because I feel that these are all important factors to a buyer when buying a car. I have also included instructions on how to use my database so that anyone who wished to perform searches would then know how to do them.
After I had designed my database there were several changes that I made. I had to take into consideration the actual file size of the database. I did not want to be taking up space, which was not being used. When choosing your design fields there is a box labelled “filed size” the default setting for this is 50. This means that for every box in the table it will allow up to 50 characters to be entered into that box. Even if you do not enter 50 characters into that box it will still put space aside for them. So one box would take up a minimum of 50 bytes. I have hundreds of boxes in my database, so this was taking up lots of unnecessary space. To change it just click in the box and choose a more appropriate number. The number I chose was 25, as this is enough space to enter in the information I needed and would also leave me a bit of space to add to the boxes if need be.
Another change that I made was to change the colour of the database so that things would stand out clearer.
Mark Austen Centre Number: 51515
I.C.T Coursework Candidate number: 8137
