Building the database objects
Download the sample application
Now that you've studied the model for the PFS database and done the analysis and design work, you're ready to start creating the tables in Access.
That simply involves copying the model to build the same structure as shown in the diagram. You won't have the data types for the fields but those should be easy to figure out.
If you need the practice with Access, by all means do it this way.
However, if you prefer to go directly to the application or you just want to study an example and see how it works, you can download the database from here: PFS Sales database and work with this version.
It may not be 100% complete but it's a very good starting point and we'll study how it was created in the next few lessons.
Creating the table maintenance formsThe fist object that we'll build is the form.
Every table in the database must have a form to manipulate the data in the table. Yes, I know it's possible to open the table in datasheet mode and do maintenance that way. But you know Access. Your customer, the final user, usually doesn't. So, we'll create a form for each table and then we'll call-up all the forms from an application menu. In Access the application menu is called "the Switchboard".
All the basic tables (Products, Suppliers, ProdLines, Reps) will have a maintenance form to do maintenance on the data: add, change or delete records.
The Sales Master and Detail tables will have a transaction form to allow only input into the Sales tables. You never change or delete sales data in the database.
Creating a maintenance form is really simple:
- select the table
- do Autoform from the toolbar
- edit the form to add labels, etc.
In our example above we added Combo boxes to be able to select suppliers and product lines from lists for each product. Obviously that means that the Suppliers and ProdLines tables will have been created first, before we can do this.