The video title is used for identifying what video is which
The classification is used to see what films are entitled for kind of ages, all video/DVD shops will need use this, if not this could turn into a illegal action.
Genre is used to identify what kind of film it is for a example the film “Dodge ball” comes under comedy.
The price shows the customer how much for renting that certain video or DVD
Membership Table
Field Name: Data Type:
Membership Number Auto number
First Name Text
Surname Text
House Number Number
Street Name Text
Area Name Text
Town/City Name Text
Postcode Text
Telephone number Text
Mobile Number Text
Email Text
Credit Card Number Text
Date of Birth Date & Time
Membership Number is used because it works like the video number; a specific number identifies the customers so it can be easy to access when renting a video/DVD.
First Name, Surname, and their whole address is a needed for a application, if they have this information it would be difficult to identify people since most people have same names and it would b also to send letters or any videos to the customer
The telephone and mobile number are for getting contacts with the customers immediately.
The email field is used to sending offers to contacts, and using the email address they can use it to open an account online and rent a video online.
The credit card number is important system to have in a video shop where this shows if the customer lives in England. Most customers would pay with credit card because not all people carry money around, and if they have forgotten their credit card it’s always stored in their account with the video shop.
Date of birth is another important thing we need to use in a video shop; this shows that the person who is renting a video is at its age to order a certain aged video. If the employee is not sure about the customer’s age he/she can check on its account and when renting that certain video the database would stop the process.
Loans Table
Field Name: Data Type:
Receipt Number Auto number
Membership Number Number
Video Number Number
DVD Number Number
Date Due Back Date & Time
Number Of nights Number
Returned Yes /No
The receipt number is used to count how many loans there is. Whenever a customer makes a new loan it is added to the list. The list starts from 1 in ascending order.
Membership is used for the same reason from the other tables shown from the previous pages. The membership table has been linked to all other tables.
Video and DVD number is used for the same reasons in the previous tables.
The date due back shows the employee and the customer when that certain video/DVD is due back. This will show that the person should bring that video/DVD back if not warnings will be sent off in a letter/email/telephone. And if still not brought back the employee can charge the customer.
Number of nights shows the customer how many nights can the customer have that certain video/DVD. If the customer needs more time watching the film he/she can pay more to have it for more nights.
Returned, this shows the employee if the customer has/has not brought the video/DVD back.
Method
Firstly I clicked on Microsoft Access,
and then I made a new database under the tile of Video.
If you want to open a database then click on “blank access database” and then you’ll need to save it under a name. E.g.
The above shows when making a new database you will need it save it somewhere and as something.
Now that the database is loaded your now ready do to your work
For this database project I need four tables, one for the membership table, the second for loans and the other two will be for video and DVD films. I will need all these tables because these are the requirements of a video shop. The membership will be needed for the customers details, and the loans table will be needed to see what videos are in rent
Soon I started making a Membership table with all the customers details e.g. name, surname, address etc.
To type something in the field name click on it then types something in e.g. Membership. And to change a data type you click on the drop down menu and choose which data type is appropriate.
The meanings of these data types are the following:
Text – this is a combination of text and number e.g. addresses
Memo – combination with numbers and text or long text e.g. description
Number – this is only using numbers e.g. telephone numbers
Date/time – this is using date and time e.g. date of birth
Currency - this is used for numbers 1-4 e.g. prices
Yes/no - used for yes or no questions e.g. true or false
OLE Object -An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data) to or in a Microsoft Access table
Hyperlink - Text or combinations of text and numbers stored as text and used as a . A hyperlink address can have up to three parts: text to display — the text that appears in a field or control. Address — the path to a file () or page (). subaddress — a location within the file or page. screentip — the text displayed as a tooltip.
Lookup Wizad… - Creates a field that allows you to choose a value from another table or from a list of values by using a or . Clicking this option starts the Lookup Wizard, which creates a . After you complete the wizard, Microsoft Access sets the data type based on the values selected in the wizard
Once all the field names and data types have been entered, you will have the foundation of your table, now all you need to do is enter the information in to the table. To get to the window to enter this information you will need to click on the design view button, indicated here:
After you have done this, it will be needed to be saved under a title:
after that click on design view this will show the table in full view
BUT before you view the results in full a window will open askin you to put up a primary key, click on yes
After clicking yes this is automatically will put a primary key for you, unless you want to do it manually
It would then come out as below:
then started filling in the customer details on the membership table
As soon as I finished that I started to make the loans table so that I can see what videos have returned and what videos haven’t returned by the people who are renting it.
I am using these data types because I will need the customers, name, address, and telephone number as this will be needed for all video companys.
After I’ve dun all the customer and loans table I needed films titles and more other details, so I made two more table, one for a video table and the other for DVDs
I will be using the data types here because it would be easy for the employee to find which customer has/has not returned a video/DVD, and also which customer has a loan. We can identify which customer has this by their membership number.
The below are the video and DVD tables which will be very helpful for the employee and the customer when they need to check what films they have and for what prices.
I used these data types because we need to know what’s the film title is and their number, and their classifications because customers need to know what films they are allowed to watch at their ages, and plus the customers need to know the prices that there are renting for.
After uploading all these information you will need to save by clicking the save button on the at times the database automatically saves your work for you.
A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). A relational database must always have one and only one primary key. Primary keys typically appear as columns in relational database tables.
You create a primary key by left clicking on the side to a column then click on primary key. After you have done that you see a little key next to that column. For example:
for all my tables I have put the first column as ma primary key
I created forms by clicking on the forms tab
To make forms you will need to click on “create by using wizard”
Click on the tables and the field that you will be using.
Click on next as you will taken to the next step where it will be asking you how you want to lay out the form
Click on next and then you will be able to choose you design for your form
After all that you will need to click next and save your form under a title.
You go the same method for the other forms that you want to create.
Click finish to see the form finished and ready to use from
To connect the table to make relationship you will need to open the database and then click on a icon like this but before you click on this you will need to click out of all the tables you have open. If you don’t have any relationships between your tables a window will appear.
If the window doesn’t come up then click on ,on the toolbar
Double click or click add for the tables you want to be connected. After youhave added everything you wanted, a window will come up.
To add a table to other tables you will need to drag the filed to the other table’s field and then a box will appear.
after you have pressed create, there will be a line linked between those fields that you chosen to connect.
In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table.
After you have finished making your relationships. Microsoft would of asked you to saved your relationship.
The way I’ve done my relationships are:
* Linked Video Number on the Video table to Video number on the Loans table
* Linked DVD No on the DVD table to DVD Number on the Loans Table
*Linked Membership Number on the membership table to Membership number on the Loans Table
The queries I have created are used to find out information quickly instead of wasting time looking for something e.g. looking up a query for customers under 21. To do this click on queries, and then click on new
After that click on design view.
choose what table you want to search.
after that it would appear like this:
where you want to do your query type in something like “>”#01/01/1983” on the criteria row and under date of birth or what ever your query is going to be. And then click to see your search/query.
To the same with other searches/queries.
I created a macro so that it would be much easier to view out the database. It would be easier for customers to check to video and DVD and how much their priced for.
To create a macro you’ll have to make a macro menu in forms.
You will then need to open a new form in design view
And then the design view will pop up.
you will need to stretch out the grid to make a macro.
you can choose if you want to have a background colour, to d this right click on your mouse and then fill/back colour, then choose colour.
Click out of the macro menu, Microsoft access will ask you to save it, click yes.
Microsoft access will then ask you to save it under a name, choose a suitable name for this e.g. Menu
When finishing that you will need to crate a macro to view everything to do this. Click on macro on the Microsoft access. Create a new macro called the similar name as you saved then macro in forms. Close all forms, tables and queries before opening the macro.
A window ill pop up, on the first row click down on the drop down menu and choose open, and do the same to the second row but choose open form.
Now from the bottom of the window select form name as the same name you saved the main menu in forms. Now save the macro as Main menu and then close the macro.
Now open the Membership form in to design view. Move the database window so that you can see the membership form and then dataset window as shown below.
Click and drag the Main Menu Macro onto the form as shown below.
Save the form and then close it. Repeat this process by adding the Main Menu Macro button to the rental info form and the film information form. Now make a macro for membership information, rental forms and the DVD and video forms. As the same process as making the Main Menu Macro.
Add the macros to the main menu as shown below.
Close all forms, tables and queries. Open the Main Menu form. Now by clicking on the macro buttons you should be able to move from form to form. You must be able be form view.
You can also make a macro to run a query.
The finished Macro should be like the below.
Evaluation
My finished database stayed with its original planning. So their weren’t any differences from my finished database and my original plan. I had to choose my fields and table names because at the end this will show as a presentation such as the Macro and not a messy work. If I would extend my database I would extend by making more queries, or maybe having the macro with more options to go to. If their was any error I would check this by going to all forms, tables and queries and check is that everything is working as they should be, or I could go to my macro and check everything is working from there. I kept my work accurate and safe by reading the instructions that I have been given and the help from my teacher.
