IT system for local video store.
Background
Mans video store, is a video store run by the Man Group. They have recently purchased 5 more video stores in the London area and already have 3 more stores including the one in Staines. We are focusing on the store that is in Staines, Middlesex; it is situated in a parade, near to many residential areas and is quite close to the town centre.
The man group has run these video stores for approximately 2 years and have found that the previous owners system was not a feasible system. The store in Staines, which we will specifically be focusing on, has a large customer base, and as always as the local population is ever increasing in the popular area, it is attracting more and more customer, with its customer base increasing every year.
The store is open 7 days a week, and its opening times are 10 am to 10 pm on Monday to Saturday, and on Sunday they are 11am to 10 pm.
Statement of the Problem to be Solved
With its increasing customer the store has found that it has had trouble with the amount of data that has to be handled manually through paperwork, thus it requires a system that can hold all customer details, a system, which the store can view all titles currently held by the store, and where the store can track customers that have not returned their rented purchases by the due back date. Searches will also be needed to be made to find a customer and/or to find a particular title; this will do through the keyboard through a simple graphical menu. The required information will be output directly onto screen or paper, this is in order to lessen waiting times for customers, and to become more efficient by reducing the likelihood of costly errors occurring.
Investigation
Interview
So that I will be able to understand more about the new system I have decided that I will carry out an interview, so that I will be able to analyses the weaknesses of the old system, how the old system operates, and the weaknesses of the old system, so that I can obtain objectives for the new system.
Preparing the Interview
An interview has been arranged with the manager of the store at May's Video Store in Staines, although the May's group owns the store, I have concluded that the manager of the store will; be the ideal candidate to interview as he runs the store everyday and therefore knows all the procedures that occur, with in the day to day running of the business.
The Interview
How old is the business?
The man group has run the Staines store for approximately 2 years
What are the opening times of the store?
The store is open 7 days a week, and its opening times are 10 am to 10 pm on Monday to Saturday, and on Sunday they are 11am to 10 pm.
How many staff are employed?
The store currently employs about 10 people with a manger working there full time.
What are the current methods of data storage and recall?
Member's details are held In a 2 boxes a black and a red box, if you wanted to retrieve the details you would have to take it out of the box.
What is the process for a member taking out a video?
When a member joins they are given a membership card with their surname on, the customers detail are held then on small sized cards, and put into a little black box, when a video is taken out, the staff ask for the customers membership card and find the appropriate customers details inside the black box, a black box signifies a member that has not taken any titles out to rent. For security reasons there is a password on the card so that nobody apart from the customer can actually take out video's, this password has to be quoted when making a rental purchase, when the password that is given is correct, the video title and date due back are written onto the card, and the card is then put into a red box, a red box signifies customer that have taken out a title to rent. Both the red and black boxes records of customer are sorted out alphabetically.
What is the process to see if any members have not brought back their video by the due back date?
To see if any rentals have not been brought back by their due back date then on a certain set day, for example Sunday, a member of staff goes through the red box looking at each individual record, and if a title has not been brought back a telephone all is made to the customer or a letter is sent.
What is the process if a customer has an enquiry about a certain title?
If a search wanted to be made for a particular video the only way would be too see if the video cover was actually on the shelf, because then on the video cover, there is a category number and then the staff can obtain the video by going to the back, and looking at the appropriate category shelf, this is not a very good idea, as if the customer or the staff couldn't find the video cover, then they would have no way of knowing whether a video is in stock or not.
Does the business have any specific company policies?
The business does have some a specific company policy and that is that all data must be secure and apply to the data protection act, therefore it must be protected from unauthorised personnel.
What are the staff impressions of the current system?
The current system is highly inefficient and much manual work is done by the staff, this in turn creates mistakes being made, customers having to wait, for example when a customer takes a title out, much time is wasted looking for the customers record writing the title and the due back date, and asking the customer a password, confirming this and switching it from the black box to the red box, this is too time consuming, and also the process is so long the errors could be made, and errors lose the company money. The second thing that is also highly inefficient is that fact that staff have to look through records manually to see if a customer has not brought their title back in time for the due date. This is highly inefficient as mistakes can be made and the fact that you have to employ someone to look through the records, this is not a cost effective option. The last thing that is also inefficient and also may make the company lose customers, is that the customer and/or staff have to check manually and see if the cover is out on the shop floor to see if a film is in stock or not, if you cant see the cover then the film isn't in stock. The whole current system in ineffective and inefficient, because we have a system that involves small cards, now these cards may got lost, be misplaced or get stolen, or get damaged, not there is no back up system to this, so this is also not a very good system.
How will a new program improve the current system?
A new system will save the company time and money, it will make the business much more efficient. The amount of data handled manually through paperwork, will be dramatically reduced. The new system will hold customer details, a system, which the store can view all titles currently held by the store, and where the store can track customers that have not returned their rented purchases by the due back date. Searches will be made to find a customer and/or to find a particular title.
Are there any computers in use at the store?
No we do not have any computers at the store.
What sort of volume of data has to be stored?
The volume of data would need to be great; it will need to hold at least about 500 titles and about 600 customers.
What information is kept on each customer?
The information kept on each customer is: name, address, telephone number, age, and password. However recently the store has stopped using the password field as many people are trying to impersonate other members, so when a video is taken out, wither the membership card, or some other proof of ID is required.
How is the current information stored?
Customer's details are held on small sized cards, and put into a little black box, which separates each customer, by alphabetical order of the surname.
Is old client data held on records?
No previous client data is held on the records.
Analysis Of The Interview
The interview carried out with the manager gave me a clear indication that there were problems with the current system that was currently being used.
The first point that came to my attention was the fact that so much paperwork and labour was involved, in a customer taking out a video for each customer there is a card and on that card the customers details are held and if they want to take a ...
This is a preview of the whole essay
Is old client data held on records?
No previous client data is held on the records.
Analysis Of The Interview
The interview carried out with the manager gave me a clear indication that there were problems with the current system that was currently being used.
The first point that came to my attention was the fact that so much paperwork and labour was involved, in a customer taking out a video for each customer there is a card and on that card the customers details are held and if they want to take a title out, the title and the date due back has to be written onto the card. This could cause problems because when a customer changes his personal details for example his address or telephone number, the card has to be either rewritten or a new card maybe required, time and money would then be wasted, as you would have to replace the card or alter it.
The cards that contain the customers details can easily be stolen or lost, this could pose many obvious problems for example security issues, the data may get to people who have no right to access it, but in the new system if a computer was used, the information could be saved and then backed up for example to a removable hard drive, so data cannot be lost.
The manager also told us that mistakes could be made in the current system, mistakes waste time and can also cost the company money, with a new system, mistakes should not be made as easily.
When a customer takes a title out, much time is wasted looking for the customer's record writing the title and the due back date, and asking the customer a password, confirming this and switching it from the black box to the red box. This is a long and time consuming method for when taking a video out, in the new system it should be quick and easy and create less waiting times for customers.
Another area what is also highly inefficient is that staff have to look through records manually to see if a customer has not bought their title back in time for the due date. This is highly inefficient as mistakes can be made and the fact that you have to employ someone to look through the records, this is not a cost effective option and is very time consuming, the new system should quickly be able to bring up customers who have not brought their videos back.
The last thing that is also inefficient and also may make the company lose customers, is that the customer and/or staff have to check manually and see if the cover is out on the shop floor to see if a film is in stock or not, if you cant see the cover then the film isn't in stock, this is not acceptable for any video shop, and certainly not one belonging to the Mays Group.
The present situation regarding equipment at the video shop is very bad as there are no computers at the video shop.
Current System
The current system is very manual orientated, for when a member joins they are given a membership card with their surname on, the customers detail are held then on small sized cards, and put into a little black box, when a video is taken out, the staff ask for the customers membership card and find the appropriate customers details inside the black box, a black box signifies a member that has not taken any titles out to rent. For security reasons there is a password on the card so that nobody apart from the customer can actually take out video's, this password has to be quoted when making a rental purchase, when the password that is given is correct, the video title and date due back are written onto the card, and the card is then put into a red box, a red box signifies customer that have taken out a title to rent. Both the red and black boxes records of customer are sorted out alphabetically.
Now to see if any rentals have not been brought back by their due back date then on a certain set day, for example Sunday, a member of staff goes through the red box looking at each individual record, and if a title has not been brought back a telephone all is made to the customer or a letter is sent.
If a search wanted to be made for a particular video the only way would be too see if the video cover was actually on the shelf, because then on the video cover, there is a category number and then the staff can obtain the video by going to the back, and looking at the appropriate category shelf, this is not a very good idea, as if the customer or the staff couldn't find the video cover, then they would have no way of knowing whether a video is in stock or not.
Problems With The Current System
Now as you can see the current system is highly inefficient and much manual work is done by the staff, this in turn creates mistakes being made, customers having to wait, this is unacceptable for a modern day video store run by the May Group, for example when a customer takes a title out, much time is wasted looking for the customers record writing the title and the due back date, and asking the customer a password, confirming this and switching it from the black box to the red box, this is too time consuming, and also the process is so long the errors could be made, and errors lose the company money. The second thing that is also highly inefficient is that fact that staff have to look through records manually to see if a customer has not bought their title back in time for the due date. This is highly inefficient as mistakes can be made and the fact that you have to employ someone to look through the records, this is not a cost effective option. The last thing that is also inefficient and also may make the company lose customers, is that the customer and/or staff have to check manually and see if the cover is out on the shop floor to see if a film is in stock or nit, if you cant see the cover then the film isn't in stock, this is not acceptable for any video shop, and certainly not one belonging to the Mays Group. The whole current system in ineffective and inefficient, because we have a system that involves all cards, now these cards may got lost, be misplaced or get stolen, or get damaged, not there is no back up system to this, so this is also not a very good system.
Requirements Of The New System
General Objectives
The system must be able to add, delete and modify and search for members details as well as the various titles that are in the store. This information will include member's personal details, and all the titles currently held by the store. The procedure to do this must be fast, and simple. There must also be a facility where you will be able to check what titles have been taken out by what members, and what date the titles are due back, there must also be a facility to print this information.
Specific Objectives - Quantitative
* Reduce waiting times for customers, so that to take out a film, it should take no longer than a minute.
* To retrieve a particular query or report it should take no longer than 100 seconds
Specific Objectives - Qualitative
* To be able to store customer's names, addresses, and telephone numbers, telephone number as well as storing what titles the storing the details of all titles held at the video shop. This is so that the store can easily locate what members they have and what titles they have, it helps also in stock levels, this can be done by using a database.
* To be able to store a particular title against a member's name, so you can easily track titles that have been taken out by a certain customer.
* So that you can use a search to see what titles have not been brought back by a member, and thus then you can track the members details and send a letter if necessary or contact the member by phone by looking up his details. You can search for a customer, about a particular title in the store and see if they actually do have the title or whether they are out of stock.
* Reduce costly errors that occur by a member not bringing a film back, or files being lost, this cannot happen with a computerised system.
* The manager must be able to produce reports through so that he can see for example which customer have overdue titles and what are the most popular rented out titles. This can be done by using reports, that are linked to a query, for example if a report wanted to be carried out to see what members have overdue video's this report will then bring up the results of the query which is carried out to see what members have overdue video's.
* A password must be on the system so that no unauthorised personnel can access it; there must also be the facility to change the password if necessary.
* The system must be user friendly, so a graphical interface is necessary.
Constraints
Hardware
Equipment - Because the video shop does not actually have any physical hardware and it does not have a pc, equipment will need to be purchased.
Software
As the store does not have any hardware, it will not have any software, it's likely that a database program will need to be purchased.
User's IT Skills and Your Knowledge
The end users will have limited knowledge of a pc, they can operate the basic functions, but they will need to be trained on the end product, and thus there input into the design of the system, will be vital as a systems success or failure depends on the end users willingness to use the system, and the end users satisfaction with the system.
Other Constraints
Cost - This will be a lot because the video shop will have to invest in the necessary equipment for example a computer, and the software required.
Time Scale - The May Group would require the system as soon as possible to improve efficiency.
Comparison of Alternative Solutions
Now I will examine the necessary solutions available to us, and from this we will be able to determine what program we will actually use to solve the problem, first we must define the actual problem, we must create a system that can meet the following objectives:
* To be able to store customer's names, addresses, and telephone numbers, telephone number as well as storing what titles the storing the details of all titles held at the video shop.
* To be able to store a particular title against a member's name, so you can easily track titles that have been taken out by a certain customer.
* So that you can use a search to see what titles have not been brought back by a member, and thus then you can track the members details and send a letter if necessary or contact the member by phone by looking up his details
* You can search for a customer enquiry about a particular title in the store and see if they actually do have the title or whether they are out of stock.
* Reduce costly errors that occur by a member not bringing a film back, or files being lost, this cannot happen with a computerised system.
* The manager must be able to produce reports through so that he can see for example which customer have overdue titles and what are the most popular rented out titles
* The new system must have a facility where letters can be sent out to the customers that have overdue video's this can be done by having a template, and only the necessary details will be changed such as the customer's details and the particular titles.
* A password must be on the system so that no unauthorised personnel can access it; there must also be the facility to change the password if necessary.
* The system must be user friendly, so a graphical interface is necessary.
Looking at the objectives it seems that a database package such as Microsoft Access will be the most viable option, however we must consider other solutions as well first lets look at Microsoft Access.
The first viable option as mentioned before is the database system, such a system is Microsoft Access, which is a database program, first lets define a database; a database is a large collection of data stored together and organized for rapid search and retrieval, now Microsoft Access is a program that manages this data, and can be used to store, retrieve, and sort information, this program ideally meets our objectives as it can store records and link data through relational databases, and a search facility is available within the machine so that we can produce queries and reports, data can be easily changed where it is stored in particular tables. A mail merge facility is also in the program so that letters can be sent to customer with any overdue titles.
The second option is to use a program such as Microsoft Excel, this particular program does somewhat include a database facility; however it would not be possible to carry out queries in this system and relationships between the different entitles would be very difficult to implement. This particular method may not also be viable, as it doesn't meet one of the main objectives, which is that the new system must be user friendly, this would be very difficult in a spreadsheet program such as Microsoft Excel.
Another option is to design a specific system using programming language such as visual basic or c++, this would be ideal as a program can also be bought directly from various suppliers, here the program has been tailor made to meet the needs of a video shop, it contains all the fields and entities required, and could meet the needs and objectives of the end user, however a major drawback would be the cost of this new system, for not only would the video shop have to purchase the necessary software, but would also have to pay maintenance costs.
Chosen Solution - Relational Database
I believe that the best system to use would the relational database system, for by using this system we will meet the end users objectives and such a system will enable us to:
* Create and modify tables, where data can be inputted via a form
* Create and modify queries, which in turn will enable us to produce reports. This was also one of the main objectives, for in this program we can carry out queries and see reports such as the most popular titles and see which customer haven't bought back which titles.
* Create and modify relationships, this would link various entities, by doing this we can link entities and tables via relationships; in a relationship we have primary keys in the tables which enables us to give a unique identifier to a certain field.
* No mistakes can occur such as duplicate membership numbers because with primary keys you can make sure that data is not entered more than once because the primary key is a unique identifier.
* Create customised input screens and using Visual Basic facilitate searches and validation procedures
* Would meet one of the end users main requirements, which is ease of use, as there would be a graphical interface using "buttons".
* A password could also be put onto the system to stop unauthorised personnel accessing the system.
* Access has a mail merge facility where letters can be sent out to the customers that have overdue video's this can be done by having a template, and only the necessary details will be changed such as the customer's details and the particular titles.
By having a relational database, data is held in tables and is linked by means of common fields.
Data Flow Diagram Of Old System
Input, Processing and Output Requirements From Data Flow Diagram
As you can see from the previous page we have a data flow diagram where we can see what the input processing and output requirements are of the old system, these will also apply to the new system as the new systems primary function is the same - to rent out a video.
Data Stores
The data stores that are held in the system are:
Members File
Titles File
Rental File
Input Requirements
Member Details
Title Details
Video Taken Out Details
Processing Requirements
Members joins store
Edit member details
Member leaves store
Member taking out video
Member returning video
Stock enquiries
Membership cards issued
Unreturned Enquiries
Most popular title rented out
Output Requirements
Mail Shots to Customers Regarding Unreturned Video's
Member finishes taking out video
Member finishes returning video
Stock Enquiries Completed.
Queries such as most rented title, overdue videos, and movies currently available.
Normalisation
Normalisation a process used to come up with the best possible design for a relational database. It is carried out so that tables are organised in such a way that:
* No data is unnecessarily duplicated (so that the same data is not held on one table.)
* Data has to e consistent throughout the database; consistency should be an automatic consequence of not holding any duplicated data.
* The structure of each table is flexible enough to enter as many or as few items as you want to
* The structure should enable a user to make all kinds of complex queries relating data from different tables.
st Normal Form
Customer
Member Num (KEY)
Forename
Surname
Tel Num
Address
Town
Post Code
DOB
Film Num
Name
Cert
Genre
Format
Charge
Copies
Rented Date
Return Due Date
Late Return
Date Returned
Fine
This is in First normal form because there is one value per cell.
2nd Normal Form
Customer
Member Num (KEY)
Film Num (KEY)
Forename
Surname
Tel Num
Address
Town
Pcode
DOB
Rented Date
Return Due Date
Late Return
Date Returned
Fine
Movie
Film Num (KEY)
Name
Cert
Genre
Format
Charge
Copies
This is second normal form because the attributes are dependent on the keys in the tables.
3rd Normal Form
Customer
Member Num (KEY)
Forename
Surname
Tel Num
Address
Town
Pcode
DOB
Movie
Film Num (KEY)
Name
Cert
Genre
Format
Charge
Copies
Order
Order Num (KEY)
Member Num
Film Num
Rented Date
Return Due Date
Late Return
Date Returned
Fine
This is 3rd normal form because there are no repeating key attributes.
Therefore we have now come to the conclusion 3 entities will exist within my system, these entities are, customer movie and order, thus these will be my 3 tables, and they will contain that data that is held under each entity in 3rd normal form.
Entity Relationship Diagram
From the normalisation process it has become apparent that there are 3 entities that exist within the system they are:
* Customers
* Rentals
* Titles
Below you can see the entity relationship diagram.
As you can see above the relationship is:
"One customer can have many orders (rentals), and a rental can consist of many movies"
Table Design
As you know I have three entities, they are Customers, rentals and titles; therefore I will have 3 tables by the name of customers, rentals and titles.
TableCustomer
After consultation with the manager of the store it was decided that member number would not be automatically incremented as the manager wanted to keep the Member number, for members already at the store the same.
Field Name
Data Type
Comments
Member Number
Number
Primary Key, No duplicate Entries
Fore Name
Text (15)
Surname
Text (15)
Telephone Number
Number (11)
I have allowed only eleven characters, because most phone numbers only consist of 8 numbers
Address
Text (40)
Town
Text (20)
Default Value = "Staines" This is because most members will come from the Staines area.
Postcode
Text (10)
Format: ">" So That the postcode appears in uppercase
DOB
Date / Time
Format: "Short Date" This is the easiest format to view and use for the store the date will appear in the format - DD/MM/YY
This table contains information about the customer, it contains all their details that are necessary for the store to run, and it contains all the details that the store currently needs from members.
TableMovie
After consultation with the manager of the store it was decided that Film number would not be automatically incremented as the manager wanted to keep the film number the same, as films that are currently at the store.
Attribute name
Data type
Comments
Film Number
Number
Unique Primary Key
Name
Text (30)
Certificate
Text - Lookup
Genre
Text - Lookup
Format
Text -Lookup
Charge
Currency
Copies
Number
This table contains information about the films that the store currently has, as you can see with each individual title, the number of copies is also listed, so you can see how many copies of each individual title the store currently has in stock.
TableOrder
Attribute name
Data type and length
Comments
Order Number
Auto Number
Automatically incremented
Member Number
Long Integer
Film Number
Long Integer
Rented Date
Date / Time
In Format DD/MM/YY
Return Due Date
Date / Time
In Format DD/MM/YY
Late Return
Yes / No
Date Returned
Date / Time
In Format DD/MM/YY
Fine
Currency
In the database, both tablecustomer and tablemovie are linked through tableorder, it is here where details are placed when a particular member takes out a video, for each order that is taken a unique order number is made.
Menu Structure Of Database
Form Design
Forms are needed in the database so that users are protected from the reality of the database, users don't need to know how data is stored in tables, they want o be able to Input data easily and efficiently and an input form does this.
Main Switch Board Form
Add New Customer Form
When a new customer enters the store, this form will have to be used. Below is an example of what the form will look like.
ORDER NUMBER
FILM NUMBER
RENTED DATE
RETURN DUE DATE
LATE RETURN
DATE RETURNED
FINE
(AUTO NUM)
Edit/Open All Customer Details Form & Find Specific Member Details Form
This is to be used when a customer is going to take out a title, you can either find the member from going trough all the records or if you have already got their membership number then you can click on the find specific members details, the form below will appear with the members details, you would already know what films they are taking out as each film has a unique film number when they hand u the cover of the film, the unique film number will be on there, so all you would have to do is input the film number and the other details. Or this is used when to delete a member and all of his information is deleted from the database.
ORDER NUMBER
FILM NUMBER
RENTED DATE
RETURN DUE DATE
LATE RETURN
DATE RETURNED
FINE
(AUTO NUM)
Add New Film Details Form
When a new film is brought into the store its details must be put into the system, on this form.
Edit/Open All Film Details Form and Find Specific Film Details Form
This is so any film details can be edited or deleted as and when necessary. Or if you are finding specific film details then that particular titles details would come up such as stock availability.
Reports Form
Here you come to the reports form page, here you can click on the appropriate button to get the necessary report.
Managers Comments So Far
After speaking with manager, I showed him the menu structure of the database, and the proposed form designs, he agreed that the way the system looked like it was going to be run is a very good idea, he commented on the following aspects:
* Less mistakes will occur as information such as taking out a video will not be put on the wrong members, as when taking out the video staff can clearly see the details of the member, he also stated that a password for taking out a film is no longer required and the date of birth of that person will be used as a password instead.
* Whereby Find specific film occurs, the lookup box should consist of the unique video number and not film name, this is because each cover that is on the shelves had the unique video number on it, so looking up a number is easier than looking up the name of the video.
* Whereby find specific member happens, the lookup box should contain the unique membership number and not the members name, as there may be duplicate name entries on the database, and 95% of video's are taken out via a membership card which the members unique member number on the card.
* He also confirmed that the forms contain all details needed for the running of the video store.
* Also when a new member joins he stated that because of the validation rules that exist, the risk of staff making errors when inputting information into the database would be greatly reduced.
This was a very positive meeting and some interesting and valuable information was gained that will be taken into account when making the database.
Queries
Queries will be essential so that the database can run and so that video store can get the most out of the database, the user requirements state that queries are essential is the system is to meet the end users requirements.
For the end user's requirements to be fulfilled the database will consist of many Queries.
There will be many queries in the database. However 2 important ones will exist these are two update queries that will exist. Update Query - Copy Loaned & Copy Returned.
Update Query Copy Loaned & update Query Copy Returned
If a customer entered the store and wanted to loan or return a video, they would have to go through the following process.
First the customers details would need to be retrieved, as you can see on the form designs, on the main switchboard form there are 2 ways of retrieving customer details, either:
* You can search through all the records and then find the customer
* Or if the customer's membership number is known you can directly go t that customers details.
You will then be presented with that particular customers form, on the form there is a command button called Copy Loaned, this command button carries out an update query this update query is copy loaned.
This is how the update query is going to be written:
Field:
Copies
Film Num
Table:
Movies
Movie
Update To:
[Copies]-1
Criteria:
[Film Number]
Or:
This means that when that particular command button is clicked the user will be asked for the film number that has been rented, and then once that is entered, the amount of stock that film currently has will be reduced by 1 as one has been already taken out, so in effect the movie table has been changed, by that particular title's stock being reduced by 1.
There is also another command button Copy returned, this is used when a customer is to bring back a copy, after it has been inputted that the customer has brought back the copy, the command button Copy Loaned must be clicked so the update query copy loaned is run. This is how the update query is going to be written:
Field:
Copies
Film Num
Table:
Movies
Movie
Update To:
[Copies]+1
Criteria:
[Film Number]
Or:
This means that when that particular command button is clicked the user will be asked for the film number that has been brought back, and then once that is entered, the amount of stock that film currently has will be increased by 1 as one movie has been brought back, so in effect the movie table has been changed, by that particular title's stock being reduced by 1.
These two update queries must be in the database because without them the stock control, which is an end user requirement, will not be fulfilled.
Queries Required to Produce Reports
The other queries that exist are the ones that are made to produce reports. All in all there will be 5 reports, so 5 queries will have to be made, these are all simple queries.
Query Customer - This is a query that produces a list of all customers at the store; it includes customer's details, which are retrieved from the table customer.
Query Movies - This is a query that produces list of all the movies held at the store, this data is retrieved from the table movie. The film name appear in alphabetical order; this was done by having the film name appear in ascending order (alphabetically starting from A), this is so if a customer asks if the store holds a particular movie then you can check the list and see alphabetically.
Query Movies Available - This is a query that produces list of all the movies in stock at the store, this data is retrieved from the table movie. This was done by asking the computer to draw up all films that are in stock, I did this by typing stock amount >0. Therefore all movies currently available in stock will appear. The film name appear in alphabetical order; this was done by having the film name appear in ascending order (alphabetically starting from A), this is so if a customer asks if the store holds a particular movie then you can check the list and see alphabetically. As you can see below, also where its says show, I have decided that the fields marked (x) will be shown, copies is not shown as this query is checking what films are in stock.
Field:
Film Num
Film: [Name] & "(" & [Format}")"
Charge
Copies
Table:
Movie
Movie
Movie
Movie
Sort
Ascending
Show
x
x
x
Criteria:
>0
Or:
Query Movies Out - This is a query that produces list of a movie currently taken out by members at the store; this data is retrieved from the table movie, the only difference being here is that u specify the films unique number when prompted so only that number comes up. , I have decided that the fields marked (x) in show will be shown
Field:
Film Num
Member Num
Return Due Date
Member: [Forename] & " " & [Surname]
Date Returned
Table:
Order Details
Order Details
Order Details
Movie
Order Details
Sort
Ascending
Show
x
x
x
x
x
Criteria:
[Film Num]
Is Null
Or:
Query Total Loans - This is so that the store manager can see the most popular films that are currently being taken out by members. The query counts each time the loans have been taken out and the results are put into descending order, so that the most popular films appear at the top. I have decided that the fields marked (x) in show will be shown.
Field:
Name
Format
"Total Loans": Member Num
Table:
Movie
Order Details
Order Details
Total
Group By
Group By
Count
Sort
Descending
Show
x
x
x
Criteria:
Or:
Macros
I have realised that for my update queries to work I will need to have some macro's that will actually run the update query this is because by default access sets up many warning parameters, this will mean that the user will be asked questions that they don't understand, so the macro will disable the parameter before the update query is ran and then will enable it after the update query has been run.
Macro Loaned Copies
Set warnings: Off - All warning parameters are disabled, to the user when the update query is run.
Open Query - Update query copy loaned is run. The update query subtracts 1 from the films particular stock field held in the movie database.
Set warnings: On - All warning parameters are switched back on.
Macro Returned Copies
Set warnings: Off - All warning parameters are disabled, to the user when the update query is run.
Open Query - Update query copy returned is run. The update query adds 1 to the films particular stock field held in the movie database.
Set warnings: On - All warning parameters are switched back on.
Anup Bansal - 1 - IT COURSEWORK
