Lesson 9 - Creating a Master/Detail form for transactions
So far we've created several tables, forms and queries. We've input data into the tables and displayed it in several ways. All the work we've done so far has been what we call maintenance. You must be able to add, change and delete all the data that you have in the tables.
Now we come to the money-making part of the operation. We will look at transaction processing. A transaction is recorded when money is exchanged for goods or services. In our business it happens when a customer rents a movie.
Before getting into the details, let's look at what we want to accomplish. When a customer rents one or more movies, we want to record the transaction in an Invoice form that will look something like this:
For future reference note that that type of form will occur in all kinds of situations: invoices, customer orders, work requisitions, purchase orders, expense reports, timesheets and on and on ...
There is probably not a single business application that doesn't use several of these types of forms.
Without going into all the theory behind it, in relational databases, of which Access is one, there is a standard table structure that is used to record this type of transaction. It's called Master/Detail.
What happens is that you use one table, the Master, to record the general part of the transaction: the transaction number, the customer ID, the date, shipping information, etc.
The other table, the Detail, will hold the particulars of each line in the transaction: the item number, the quantity, the price, etc.
The actual structure of the tables will vary slightly depending on the application. In our case, for example, if a customer rents 5 movies the "Out" date and time is the same for all the movies but the return date and time may be different for each: some may be 1-day rentals and others 7-day. So, we keep the Out date in Master but the Return date is in Detail. And we don't have a quantity since there is always only one of each movie.
Now, if you take another look at the tables you should have in the database it should start to become clear.
A few points to note when building the tables:
The Northstar application comes with Access. It is the standard demo for relational database construction. You should normally find it in the folders where you installed Access.
Northstar is a wholesaler. Therefore it has all the standard tables and forms we've been talking about here: Master/Detail, customer order, invoice, etc. Take the time to study the application and to compare it with what you want to do with MikeVideo.
Building the form
The Invoice form at the top of this page is a rough draft of what we really need. If you use the AutoForm option that's what you get. But it's far from complete: it doesn't show the customer's name and address, the employee's name, the invoice total, taxes, etc.
We can fix all that but it will require some work.
This lesson is probably the most difficult in the tutorial, so pay close attention!
There are several ways to proceed. We could use a Form Wizard, or do the whole thing manually or a combination of both.
We'll use the combination approach. This way, you'll understand all the little tricks and you should then be able to reproduce the technique in all kinds of applications.
Here is the procedure, step-by-step:
Next week: The Application Menu. See you then!