Many database projects will require far more than two or three tables. The information contained in a database is likely to be extensive and the rules of relational database design (see notes) will ensure that there may be dozens or hundreds of related tables in a typical system. Commercial systems may have hundreds of tables and millions of records in each one, for example every phone call, whether on a mobile or a land line is now recorded and itemised on customer bills, which makes quite a lot of records. In these notes we are concerned with the principles of database design so we create much smaller examples.
It's all very well to build a database that provides us with information on something like CDs and track details but the design is, as yet, of little practical use. Our system would be much more useful if it could record sales of some kind, either CDs in the way on-line shops do, or tracks in the way that a service like iTunes does.
To achieve this we need a new table to store customer details so that goods and invoices can be dispatched and charges levied (probably electronically).
Each time a customer orders a CD or a track an order must be created:
The CustomerID field is created with the Lookup wizard. The Orders table requires only the CustomerID field from the Customers table (a name would not identify a customer uniquely and additional information about the customer in the Orders table would break the rules of realtional database design). Also, it would not be very easy for a user of the database to connect a number with a name; the ideal solution would be to display the customer name but store the required CustomerID, something that Access lets us do.
In fact the name is not enough to identify a customer uniquely, we need the address as well (it is still possible for two people of the same name to live at the same address, father and son for example, but let us ignore this for now). Access will allow us to include two fields in the lookup, one for the information we require (CustomerID) and the other to help us choose the information we need. There might be many customers called John Smith so we should include the address as well, but this exceeds the number of fields available. We therefore need to build a query that collapses the data from LastName-FirstName-Address (and date of birth if necessary) into a single field so that we have the required two fields for the Lookup wizard.
Create a query based on the Customers table. Add the CustomerID field and then, for the second field, click the Build button on the toolbar (the wand) and create an expression that includes the fields we require. Double click the tables button on the left and open the Customers table from the list. The name of the field is 'Expr1'. To add a field name Double click it; to concatenate fields (add them together) click the '+' button or type a '+' symbol and then double click another field; to add a separator enter a '+' symbol and a symbol such as a comma between single quotation marks.
Save the query and check that it produces the required results. We are now ready to follow the Lookup Wizard for the CustomerID field. Click the Queries button to display the list of currently available queries:
Add both the fields that you included in the query:
Set the width of the fields as they will appear when the data is looked up:
Make sure that the data stored is the CustomerID:
The Date field can make use of a Default value. The date most likely to be used in the order details is the current date so simply set this to '=Date()'. You can either type this in directly or use the Builder by clicking the dieresis on the Default Value line. Double click 'Functions', click the '=' button )or type it), choose 'Date' and add brackets after it.
Now the date will be added automatically when you access a new record; the current date can, of course, be changed if required.
This completes the Orders table and we should now create the OrderItems table.
Before we create this table we should create another table to provide details about the artists that recorded the CDs. This is simple:
We could add more detail to this later but this branch of the music database raises lots of new problems. CDs are released under an artist's name but that artist may be a solo performer or a group or band (the same situation arises in other types of music such as classical and jazz). The information we would store on an individual is different to that we would store on a group. We might want to include all members of a group in the artists database but many group members may not have issued a CD in their own name so they would not appear in the CDs table. Some individuals may appear in more than one group and may return to it on more than one occasion. Incorporating this complexity into the CDs-Tracks database is difficult so we will limit ourselves to something much more simple. The Memo field will allow us to record any information relevant to the artist or group in an unstructured way.
Notice that the primary key is ArtistName - are there any duplicates in the world of entertainment?
Now back to the OrderItems table. Each order will contain a number of order lines, which in this case hold details of the CDs or tracks purchased:
The OrderItem field is constructed with the Lookup wizard, based on the TrackDetails field from the Tracks table.
The Artist field is also constructed with the Lookup wizard, based on the new table for Artist Details.
The OrderID field can be taken from the Orders table when we create a composite form that includes the Order and the Order Lines.
The relationships diagram should now be updated by adding the new tables we have created.
The tables we have created in this section should each have a corresponding form. The form for the OrderItems table should be in tabular format:
We can now add this form to the lower part of the Orders form so that we can see the items or order lines for a particular order and we can add them on screen.
To create the composite form for Orders and OrderLines:
To display customer details such as name and address, make sure there is a form based on the Customers table and then add this to the composite form, to the right of the Orders information. If you want your users to be able to update customer details from this form then leave the properties of the fields in this table as they are. To protect a field on a form from changes by the user select the item, right click it, choose Properties and change the Locked property to Yes.
Add some labels to improve ease of use and a line to divide the customer details from the order section. The finished table might look like this:
Note that this form does not allow for the creation of new customers because it does not include the CustomerID field. Users will have to refer back to the Customers Form or Table for this.
When an order is complete an invoice should be raised for dispatch to the customer. Twenty years ago invoices would have been posted but in this era of electronic commerce it is far more likely that they would be sent by email (include the email address in the Customer table!). We want buttons on the form that say 'Print' and 'Send', one of which produces hard copy and the other sends an email to the customer giving details of the order.
For now let us consider how we get printed output from the form so that we can print an individual order.
The first thing that we need is a report that will display and print the contents of the form, that is the details of the order from the Orders table and the details of an order from the OrderItems table. These have been collected on the Orders form shown above. To create a report based on these related tables we need to build a suitable report.
We construct a report containing related tables in a similar way to that used to build a multi-table form.
First, make sure there is a report for the OrderItems table so that it can be incorporated into the Orders report. This report can be built using the wizard.
Second, start another report using the wizard, this time based on the Orders table.
There is no need for grouping or sorting as an invoice will show only one order at a time:
Choose tabular layout and complete the new form with the default settings.
Switch to design view and drag out the form borders to make room for the OrderItems sub-report.
Accept the link offered by the wizard, which will link the data in the main form to that in the subform (orders to order items):
This will give us a report that includes order details and order items.
We can go further and add customer details. To do this, create a simple report based on the Customers table and then add this report to the composite report as before. The labels may need to be re-written and the layout customised to produce a pleasing effect:
With this report finished we can now use a macro to call it with a filter that will display just one order record in the report.
To do this you must first create a query that will identify the one record required for an invoice. One way to do this is to use a parameterised query, as follows:
Save this query and then go to the Macros section of the database control panel. Click on 'New' and choose 'OpenReport' from the Action list. Fill in the details in the boxes below. Choose the report you created earlier from the list of reports. Set the View to Print Preview and the Filter to the one you just created.
Now add this macro to the form as follows:
Open the Orders form (the one with Orders and OrderItems in a sub-form).
Click on the Command button icon in the Toolbox.
Drag out a button on the form underneath the order items sub-form. Choose Miscellaneous/Run Macro from the options:
Choose the macro you want to run from the list:
Enter the text for the button or choose an icon:
When pressed this button will now run the query you specified to identify the order record and the report you specified to print a copy of the order details.
There are many more macros available to achieve a wide range of effects in a database application.
This completes the form required for the input of orders of tracks to the system. We might have created a form for ordering CDs
We can create a Data Access Page for Orders based on this form.
The main difference between this data access page and the original Access page is that the Customer ID combo box does not display the name rather than the CustomerID field - the code that achieves the lookup from the query is not carried across into the data access page.