One form to many Tables

[techflexus]

Okay, I'm trying to set up my database correctly.



I have table for clients. And a table for employees. Each client has any of four possible classes of employees that could see them for different reasons. In order to run reports and stuff i figured out i had to create more than just two tables... here is what i have. in simplified form

Client Table
____________
ClientNumber
Name
Address stuff
EmployeeClass1ID
EmployeeClass2ID
EmployeeClass3ID
EmployeeClass4ID


EmployeeClass1HoursTable
______________________
PrimaryKey
ClientID
EmployeeID
NumberofHours

3 more identical tables for the other 3 classes

Then

EmployeeTable
_________________
EmployeeName
Class
AddressStuff


What I want to do is have one single form that we can enter data about a client that data would then save to the client table and possibly the four other tables for each class of employee so I can reference Which clients have what employees for how many hours. I don't want to use subforms because all i get with those is an ugly usesless table on the form. How do I make a form that looks nice and saves to all 5 places at once?????


Thanks in advance....

[John_G]

Hi -

Your table structure is not properly normalized. Any given table should have data pertaining only to that item. For example, look at your Client table; it should contain fields which describe only the clients - the Employee fields you have do not describe clients, therefore should not be in that table.

You don't need 4 tables for the hours; one will do since you have the employee class in the employee table.

EmployeeVisits would contain:

EmployeeID
ClientID
... details about the visit: date, hours,

The main form in all this should be one that records data regarding employee hours (visits). You won't want to use that to enter/update client data, since there will be much more data about a client than you would use for recording the visits. The same is true for the employee data. You should have separate forms for the Clients and Employees.

For reports, proper use of querys and filters on them will enable you to get the reports you want.

Once you sort the table structure out, the rest should follow fairly easily.


HTH

John

[techflexus]

Alright so If I'm hearing you correctly I need this...

Client Table
___________
ClientID
Name
address
phone
blablabla

HoursTable
____________
clientID
EmployeeID
NumberOfHours


Employee Table
______________
EmployeeID
Name
Address
Phone
blablabla


Is that correct??? if so then can you point me in the direction of a tutorial on how to make a form that would include all the client stuff and which employees are associated with their number of hours. When i try to do sub-forms the stupid things are always in like a table format.

thanks