merging 2 tables

[Guest]

Hello,

I have 2 tables that need to be merged. The tables store staff data based on there program data. for example if staff work in drug and alcohol. The said table, say table1 stores this info: hours worked, showed(#client hours), cancels, no shows etc
and a percentage based on productivity. However there are some staff who are in drug/alcohol and intensive outpatient for instance. I need to store this info into a second table and somehow add the hours worked from the second table to the hours worked in the first table and output the results to a single query. My report is based on a single query.
All in all, I need total number of client hours a staff scheduled. This will ofcourse raise the productivity percentage, showing the staff as being very productive.

Help

Thanks

 

[Kevin Sprinkel]

All in all, I need total number of client hours a staff
scheduled. This will ofcourse raise the productivity
percentage, showing the staff as being very productive.

A general principle of database design is to store the
same type of data in a single table, differentiating
subgroups by the value of a field. Since the existing
table and your proposed second table store the same type
of data (hours worked), I think a single table would be
appropriate.

The easiest way to implement this is to add an integer
ProjectType field to your existing table to distinguish
between the various types of billable hours.

Then create a new table with the following structure:

ProjectTypes

FieldName Data Type Sample Values
---------- ---------------- --------------
PTypeID Autonumber 1, 2, etc.
PTypeDesc Text "Drug/Alcohol", "Outpatient
Support", etc.

On your data input form, place a combo box bound to the
ProjectType field. Tell the wizard to get its values from
the new ProjectTypes table, and hide the key field.

Before adding new data, run an Update query on your
original table to change the value of ProjectType to its
appropriate value (the integer representing "Drug/Alcohol"
in the ProjectTypes table).

For the totals, create a query selecting

Employee Group By
Hours Worked Sum

to get the total number of hours worked by a given
employee.

If there are other reasons to keep these tables distinct,
create a query from the two tables, joined on employee
number. Select both Hours Worked fields. Create a
calculated field by entering the following in an empty
column:

TotalHours=[FirstTableName].[HoursWorked]+
[SecondTableName].[HoursWorked]

Again, group on Employee number and sum on TotalHours.
Uncheck the other two fields.

HTH
Kevin Sprinkel
Becker & Frondorf