Software Catalog:
The Related Tables



Software Catalog: Table Layout

More Tables

Before we dive into the next part of the application let's take a look at the projected system of tables and how they relate to one another. The table above shows the planned tables with their field names.

Notice the related field names that appear in the various tables. For example, MfgID appears in the Systems table and in the Software table. We'll use that to tap into records of the Manufacturers table in reports.

The Systems table identifies the location of the software and it is a stub for possible added tables and reports tracking hardware. This could include network workstations, spare parts, etc. For now we'll use it to identify the computers where our software is installed and locations of programs still in the box. If only one computer is involved it really is a useless exercise, but the purpose here is to sample a relational database system. If some software is on the shelf uninstalled or there is more than one computer it should be helpful to be able to pull a list of software for each computer or a list of software and its locations.

Though the Vendors table seems a duplicate of the Manufacturers table, the purpose is to track the resellers you do business with.

The Programs table describes each program. Is it a word processor, a custom accounting or inventory-tracking program.

The Software table ties all this information together. Several of the fields are ID fields from other tables. Because prices vary for the same program depending on time and vendor, and the computer location will vary, a record will be entered in the software table for each copy of a given program that is cataloged.

Our next job is to create the remaining tables.

Vendors table

We'll create the Vendors table the easy way. We will just copy the Manufacturers table. Here's how: Select the Tables tab, then select Manufacturers table. Press Ctrl+C to copy the table to the clipboard. Then press Ctrl+V to paste it. Type Vendors in the dialog requesting a name for your pasted table. Click the radio button for Structure Only so that your new table will contain no data.

Select the Vendors table in the database window and press the Design button. Now move to the Field Name property of MfgID. Highlight the Mfg part and replace it with Ven. Copy Ven to the clipboard. Skip down to MfgName and highlight the Mfg part and paste Ven over it. Do this with each field in the list. Delete the Address part of VenMailAddress so that it becomes VenMail. Leave the Update field name unchanged.

In the Description column replace the "M with a "V for the first field. Change the Default Value property of VenID to VXXXX000 and change its Caption to Vendor ID. Make VenID the key field by selecting the left margin and pressing the Key button, which is somewhere near the Help menu. Save your work.

Systems table

Next is the Systems table. A way to save some work with the Systems table is to create an empty table and copy and paste fields from another table. Open the Vendors and Manufacturers tables in design view then minimize the windows. On the tables page of the database window press the New button. Choose Design View. Move things around so that you can click on the each window. Restore the minimized windows. Copy the field VenID to the 3rd field of the new empty Table1. Copy MfgID to the 4th field in the new table. Be sure you click the margins so that the complete rows are highlighted.

Click somewhere else to deselect the pasted fields. Now select the margin of MfgID and drag the field so that it is above VenID. Select the margin of MfgID, press the insert key to open a new field in 2nd position. Copy VenID to the 1st position and rename it "SysID". We're doing a lot of seemingly useless maneuvering here, but it is to give you practice in modifying table structures and reusing what you have already done.

The Description of SysID is "Y-" plus 1st 4 characters of system name plus 3 numeric characters. Change the Default Value to YXXXX000. Remove the Default Value of VenID.

Select the SysID field and make it the key field. Save this table as Systems.

Copy the VenName field and paste it to field two and field five (after SysID and VenID). Change the name of the first to SysName and the second to SysUser. Change the Field Size property to 30 for both new fields. Change the Descriptions to System Name and User Name respectively. Make similar changes in the captions properties.

Type SysCPU for the next field name. Make it a 25-character text field. Type "CPC type and speed" for the description.

The next field will be SysRAM, the number of megabytes (or kilobytes). Make it 5-character text.

Copy and paste the Update and VenSel fields from Vendors. Change VenSel to SysSel. And make appropriate changes to the description and caption. Leave the Update field unchanged. Save.

Programs table

We're Ready to start the Programs table. Make the Tables page current in the database window. Click the New button and click Design View in the resulting window. Click OK. Move Table1 design window so that you can get to the database window. And open Manufacturers in design mode.

Select the left margins of MfgID and MfgName and copy them to the first two rows in Table1. Replace the Mfg portion of the field names with Prog.

Change the ProgID description to read "P-"plus 1st 4 characters of program name in upper case plus 3 numeric characters." and the ProgName description to Program Name. Make appropriate changes to the captions. Change the default value of ProgID to PXXXX000.

Type the Field Names, select Data Types and type Text Field Sizes of the next ten fields as listed below:

ProgCategory:
ProgType:
ProgPlatform:
ProgVersion:
ProgDate:
ProgExeSize:
ProgComment1:
ProgComment2:
ProgNotes:
Text...............25
Text...............25
Text...............20
Text...............10
Date/Time .... Short Date
Number ....... Long Integer
Text...............50
Text...............50
Memo

Copy Update and MfgSel from Manufacturers. Change MfgSel to ProgSel. The description of ProgDate is Date of program executable file and ProgExeSize is Size of main executable file in bytes. Type appropriate captions for these fields. Make ProgID the key field and save the table as Programs.

Software table

The Software table will tie things together and add some detail about each copy of the program in your library. This is where you can record several copies of the same program. The vendor and cost may change from one copy to another, and the computer system will likely change from one to another.

Open a New table in Design View. Copy and paste the key ID field from the Programs table to each of the first two fields. Change the name of the 2nd field to SoftID and make appropriate changes. In this case the ID description will be Ordinal number string beginning with 0001. The input mask is 9999;; and the default value is "0001". The SoftID field will not be the key.

Leave the name ProgID in the 1st row. Copy VenID from Vendors, MfgID from Manufacturers, and SysID from Systems. For all IDs except SoftID change the Indexed properties to Yes (Duplicates OK) otherwise you could only have one entry per vendor, one per manufacturer etc.

Select MfgID and press the insert key to open an empty row above. Copy ProgName from Programs table. Name it SoftName and make it a 50-character text field.

Create the other fields by typing the Field Name, selecting Data Type and typing Text Field Size for the names listed below:

SoftCost:
SoftComment1:
SoftComment2:
Currency
Text.........50
Text.........50

Copy Update and paste the Sel field from one of the other open tables and change the name of the Sel field to SoftSel and leave Update as it is.

Microsoft Access will offer to create one every time you close the design window if we don't have a key field. Select the first field (ProgID) row and insert a new row in first position. Name the new field something like MyKey or whatever. It is not likely to be used anywhere. Make it an AutoNumber field. Access will increment the number for each new record. You have a key field to make Access happy, so everybody's happy, and we've created all of the planned tables for the application data.

TableLookUp table

We need to construct a small table to hold information regarding the data tables. Later our program will access this information.

Open a New table and type the following names of text fields: TableCode, TableName, TableID, and TableItemName. Field sizes are 1, 20, 8, and 50 respectively.

Descriptions are: Unique one-letter code to identify each table. Data table name; Name of ID code field; and Name of Name field.

Make TableCode the Key field and save it with the name TableLookUp. Click the button with the datasheet icon, on the tool bar second row below the File menu to change to datasheet view. Save the table again if prompted to do so.

Now we'll get the data for the lookup table: Open Manufacturers in design view. Arrange the windows so you can see the Manufacturers' field names and a new empty record in TableLookUp.

Data for the first record follows: M, Manufacturers, MfgID, MfgName. Now do the same with the other tables. Take them in alphabetical order as they are listed in the database window:

Programs: P, Programs, ProgID, ProgName; Software: S, Software, SoftID, SoftName; Systems: Y, Systems, SysID, SysName; Vendors: V, Vendors, VenID, VenName.

The table information could easily be hard coded in the Visual BASIC code, but it would make it much more difficult to extend the application later. This way more tables can be added and additional information fields can be included as the need occurs. And the information is stored all in one place.

Next we'll write some code then construct some more data entry screens.

Return to Beginning
Continue
Previous
This document authored by Pat Tyler
Copyright July 1997