Database Design – Assignment 1

Downloads

18.1-database.accdb 18.1-macro.vba 18.1-logo.png

Validation Rules and Input Masks [P4]

Field Validation Rules and Input Masks
The driver's rates vary from £40 to £80 per session and their wages are paid by the firm each month. >=40 And <=80
The first time customers make a booking they are given a client ID which consists of the first three letters of their surname plus a four figure number. Customers quote their ID when they make further bookings. Regular customers can negotiate discounts. >LLL0000!
Date of session >=Date() And <Date()+90
Number of passengers >=1 And <=20
Date booking made Date()
Payment made >=0
Postcode >LL09\ 0LL!
Customer and driver titles "Mr." Or "Mrs." Or "Ms." Or "Miss" Or "Dr."
Driver NIN >LL000000?!
Sessions "M" Or "A" Or "E"

Importing Data Into Access [M2]

The following images show the process I took to import data from a text file into a Microsoft Access database.

Firstly, the importing dialogue box is opened, and the user prompted to choose the imported data's destination. This is most often a new database table.
Image by me; see license.
The user must give Access a reference to the file they wish to import before proceeding.
Image by me; see license.
Next the user is prompted to tell Access whether the data is delimited by fixed-width columns or a delimiting character (generally a comma).
Image by me; see license.
One must then select how exactly the data is divided into columns. I chose Comma from the list of delimiters, and ticked the First Row Contains Field Names box. This takes the first row of the file out of the table, and uses the values as column headings instead.
Image by me; see license.
Next, one is able to refine the naming and data types of the columns, potentially correcting any auto-detection mistakes made by Access. I chose to change the indexing rules for the primary key column.
Image by me; see license.
The next step is to select a column or create a new column to hold primary key values. These are used to uniquely identify different rows of a database, meaning that no repetition is allowed. I used the Stock ID column for this purpose.
Image by me; see license.
Lastly, one must name the new table that will hold the imported data. I would normally add a tbl_ prefix, but I did not as the table would be deleted once the contents had been analysed. Were I not going to tick the analysis wizard box at the bottom of the window, I would have added the prefix.
Image by me; see license.
If one ticks the analysis box in the Import Text Wizard, Access will show the Table Analyser Wizard. Here I changed the name of the main table to a more suitable one, including a tbl_ prefix.
Image by me; see license.
I then selected the fields of the table that needed to be moved out. This included all of the information about item suppliers.
Image by me; see license.
I dragged the fields into their own table, and named it appropriately.
Image by me; see license.
I then set the correct field to be the primary key for the new table.
Image by me; see license.
Once the data had been analysed and split into the correct tables, I closed the wizard. I did not choose to save the import steps I took as the importing process was a one-off.
Image by me; see license.
The Table Analyser Wizard also gives the option to create a query which will show the data as it as originally imported. This would server no use in my situation, so I did not ask Access to create it.
Image by me; see license.
As the data had been successfully split into the correct tables, the original imported table was no longer needed, and I deleted it.
Image by me; see license.
Above is the final relationships diagram for the Denim Daze database.
Image by me; see license.
For the S-Cars database I told Access to open the data analysis wizard again, allowing me to split the data into multiple tables.
Image by me; see license.
I did not use the analysis wizard for the tbl_drivers table import. This is because there is little to no repetition in the data, and it is therefore efficient enough to begin with.
Image by me; see license.

Database Design [P2] [P3]

I started working through each table changing settings for the different fields. I changed field names while removing redundant information, added input masks to the fields, added validations rules and error text, and set most fields as required fields. The input mask shown for the BookingID allows for four upper-case letters followed by four numeric digits. All characters are mandatory.
Image by me; see license.
Although not shown in the screenshot, the ID column in the customers table would have an input mask of >LLL0000!, which would require three letters and four numerical digits to be entered.
Image by me; see license.
The National Insurance number field in the tbl_drivers table also uses an input mask. This required two letters to be entered, followed by eight numerical digits. A further letter is permitted, although not mandatory, as the last digit in an NIN is a checksum and is sometimes omitted.
Image by me; see license.
Auto-incrementation was also configured for some fields, such as the ID column in tbl_sessions. This means that every time a new record is added to the table, the primary key column is automatically generated.
Image by me; see license.
As car registration numbers do not take a consistent form, an input mask cannot be used for this field. The field settings were still changed however, making the field a required one, for example.
Image by me; see license.
The final Relationships view of the database, showing the tables and contained fields.
Image by me; see license.

Input Forms [P6] [M4]

When the Form Wizard is invoked, one must choose which fields from which table(s) and/or query(ies) to use in the form.
Image by me; see license.
In the case of the S-Cars database, I decided to use all of the fields available in the tbl_bookings table.
Image by me; see license.
I chose to use a basic columnar layout for the form, as it was of minimal complexity. More detailed and capable forms can utilise more suitable and user-friendly layouts.
Image by me; see license.
The next and final stage of the wizard allows you to give the new form a name. I chose frm_bookings, following the previously-used naming convention.
Image by me; see license.

Access SubForms

Following the initial creation of the bookings data entry form, I decided to add a sub-form. The first stage of Access' SubForm Wizard asks one to select a field or fields from tables or queries, which will become the fields shown in the sub-form.
Image by me; see license.
I chose to use all of the fields in the tbl_sessions table.
Image by me; see license.
When prompted by the wizard whether I wanted to create a new form or add the sub-form to an existing one, I chose the latter option, using the tbl_bookings table I created before.
Image by me; see license.
The wizard also allows one to name the new sub-form. I used frm_sessions, following convention as before.
Image by me; see license.
I also enabled conditional formatting for the NumberOfPassengers field in the sub-form. This makes it instantly clear which bookings have a greater or smaller number of passengers.
Image by me; see license.
Five value brackets were used, grouping the bookings into either the red, orange, yellow, lime or green group. A gradient colour scale would have been more suited, but this feature is only available in Excel, and not Access.
Image by me; see license.
Buttons, useful for a host of tasks, can be added to an Access form from the ribbon.
Image by me; see license.
Buttons were added to the database form allowing the user to navigate backwards and forwards through the records. Buttons were also added or going to the first and last records.
Image by me; see license.
Either text or an image can be used on buttons. For the navigation buttons, I chose to use icons as I feel the symbols are widely recognised enough.
Image by me; see license.
The button wizard also requires for buttons to be named, and I named the buttons I created with a prefix of the form name. This would make them clearly distinguishable in a list of buttons in a bigger database.
Image by me; see license.

Macro Code

The VBA code for the macros can be downloaded in the 18.1-macro.vba file available above, and can also be viewed below.

Option Compare Database


' Click event handler for PaymentMade on tbl_bookings

' This function generates a random ID for a new booking when the user clicks
' in the "Payment Made" field.

Private Sub PaymentMade_Click()

    ' only create an id if it's needed
    If IsNull([BookingID].Value) Then

        ' variable declarations
        Dim Chars As String
        Dim Alpha As String
            Alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
        Dim Ref As Integer

        Dim Digits As Integer
        Dim DigitsStr As String

        ' add a random letter on, four times
        For n = 1 To 4
            Randomize Timer
            Ref = Int(26 * Rnd) + 1
            Chars = Chars & Mid(Alpha, Ref, 1)
        Next

        ' generate a random number
        Randomize Timer
        Digits = Int(10000 * Rnd) ' 0-9999

        ' convert number to string
        DigitsStr = Trim(Str(Digits))

        ' zero-pad id string
        For j = 1 To 4 - Len(DigitsStr)
            DigitsStr = "0" & DigitsStr
        Next j

        ' concatenate the four letters and four digits
        [BookingID] = Chars & DigitsStr

    End If

End Sub


' AfterUpdate event handler for NameLast on tbl_customers

' This function will fire after the content of the NameLast field has been
' changed. The function takes the surname of the customer and a random number,
' and generates a (likely) unique customer ID.

Private Sub NameLast_AfterUpdate()

    ' variable definitions
    Dim CustNameClip As String
    Dim CustId As Integer
    Dim CustIdStr As String

    ' cut the first four characters, and convert to upper-case
    CustNameClip = UCase([NameLast].Value)
    CustNameClip = Replace(CustNameClip, " ", "")
    CustNameClip = Replace(CustNameClip, "'", "")
    CustNameClip = Left(CustNameClip, 3)

    ' pad name string
    For i = 1 To 3 - Len(CustNameClip)
        CustNameClip = CustNameClip & "X"
    Next i

    ' generate a random number
    Randomize Timer
    CustId = Int(10000 * Rnd) ' 0-9999

    ' convert number to string
    CustIdStr = Trim(Str(CustId))

    ' zero-pad id string
    For j = 1 To 4 - Len(CustIdStr)
        CustIdStr = "0" & CustIdStr
    Next j

    ' concatenate the two halves of the id, then set id field to output value
    [ID] = Trim(CustNameClip) & Trim(CustIdStr)

End Sub