- Item ID – Lookup to stock table for the ID of a stock item
- Individual Stock Number – The stock number of the particular item
- Customer No – Lookup to the customer table for the customer id number
- Status – Current status of the item, available, on loan, or in for repair
Stock Table
- Item ID – Two letter mnemonic code for item e.g. WJ for wheelchair – junior
- Description – A description of the item e.g. Wheelchair – Junior
- Cost Of Replacement – The amount of money required to replace an item
Relationships
The Image below outlines the relationships in my database.
Data Capture Forms
Customer
Stock Table Linked to Hire Table
Queries
I will make the following queries;
Overdue – Find Overdue items, and who currently has them
Due back this week - Query to find items which are due back within a week.
Stock Information - Query to show information on a selected item, by using a prompt for item ID and item stock number.
Weekly Stock Report - Query which will list all stock and its current location, status etc.
Update Date Due Back - Update query to update due back field in hire form.
Reports
I will make reports of the following queries;
- Date Due Back This Week, date due back sorted into ascending order
- Stock Information
- Weekly Stock Report
- Overdue
Method Of Data Entry
To create a new customer
- Click on the create new record
- Customer No automatically goes to the next autonumber
- Fill out the rest of the fields, the ward number is a lookup for the ten wards.
- The phone number has validation to make sure it is 11 characters long
- Title is a lookup, which defaults to Mr
To add new stock item
- Select Add new item
- Enter Details
- Stock No must be present
To add a new rental
- Scroll through on the stock table to the desired item
- The hire table displays all of that item and status etc
- Change the status from available to on loan (L), select customer ID from the lookup and enter Date out
- Click Calculate Date Due Back button to calculate date due back
Validation And Verification
I will use validation in my tables, e.g. length check for phone number, and the presence check for stock number. I eliminate a lot of the need for validation with lookup functions for example title in customer table, and item ID in hire table. This helps to avoid unnecessary mistakes, and prevents unneeded duplication.
Testing
I will now show hard copies of the database to prove that it is working correctly.
This is the Customer Table
The lookup function shows what a patient currently has on loan.
Miss Russell currently has two junior crutches, a junior neck brace and a junior wheelchair which are due back on 23/02/2003 and 13/02/03 for the junior wheelchair. The wheelchair is currently overdue, and my overdue query will show this.
Miss Russell’s other items are due to be returned within the week, so they should be on the Due This Week query, I will show a printout to prove my project is working correctly.
The stock information query below shows that the junior wheelchair, stock #1 is in Miss Russell’s possession.
I will now show that Miss Russell has returned the wheelchair, however it needs repaired, and Miss Russell requires a replacement.
I will change the settings on the Stock – Hire Linked table, and click the calculate Date Due Button
These changes now appear in my tables and my weekly stock report.
I will also show how I can add new items and customers.
I will add a Mr Wilson, living in Gall Street, and in ward 5. This also shows my phone number validation, as I have purposely entered 11 numbers instead of 12.
My main table now shows Mr Wilson.