Table design for shared data

[Jaycee]

I want to design a db that will store training data
(which includes travel expense) and travel expense data
(which includes travel for training). For example:

Training:

PersonA
Course1
Location
Fee
Budgeted travel expense
Actual travel expense

Travel:

PersonA
District Meeting
Location
Budgeted travel expense
Actual travel expense

Each record will include more fields than shown in the
examples, so it's too much data to include on one form.
I'm sure this is elementary, but I'm drawing a blank on
how to have separate forms for training data and travel
expense data but have the training-related travel expense
fields shared between them.

Any help is greatly appreciated!

Jaycee

 

[kevin watkins]

Jaycee,

What you want is to reduce the amount of information that
is in any one table. Have one table as travel expences,
one table for person, one table for training. Then as you
create the forms you can have subforms, or buttons that
link to new forms. As the user is putting the info into
the form through your switchboard or what ever means you
use to navigate the database, they can go to specific
forms and the have buttons on those forms to take you to
the other forms.

By having the travel expences in one table you can easily
have them show up any time the person is used as a
criteria. Then any travel record associated with the
primary key of that person will associate the travel
expence records. Then you can have further fields that
you can use to set criteria on. So if it is just for
training and you have a check box in this field. You
could use that as criteria to norrow down what is reported
in a different location.

As you use the forms wizard you can pick and choose the
fields that you want to have on your form. You can pick
form multiple tables, or just one table.

I have just found that the tables need to be specific for
the type of infomation in them. I would not have the
district meetings field in the same table as the travel
pay fields. it could cause multiple entry if say you had
6 people attend the same district meeting, you would have
to input that info multiple times. Rather have the
district meeting in one table, then use pull downs for the
user to select which district meeting they attended, this
would establish the relationship via the Pkey and Fkey.
Keeping the information minimal in that table.


Hope this is some help

Kevin

 

[John Vinson]

Each record will include more fields than shown in the
examples, so it's too much data to include on one form.
I'm sure this is elementary, but I'm drawing a blank on
how to have separate forms for training data and travel
expense data but have the training-related travel expense
fields shared between them.

Well, for starters, don't confuse the function of Tables (the data
storage medium) with Forms (tools to manipulate data in tables). It is
not necessary, nor even very typical, to have one Form for each Table
with all the fields on it!

If you design your table relationships correctly, the Forms will
follow. Identify the Entities - real-life persons, things, or events -
and create a Table for each of them. Some of the entities I see here
are:

- Employees (well, of course)
- Training Events
- Trips

There would be a one to many relationship from Employees to Trips, and
a many to many relationship from Employees to Training Events (one
Training Event may be attended by multiple employees, and each
employee may attend more than one event); so you'll need an
EmployeeTraining table.

The Trips table and the EmployeeTraining table would have a currency
field to record the cost of that specific training trip; similarly the
Trips table would have the cost of that trip. The Employee table would
probably have a field for the allowable expense for each category for
that employee.