2 Tables, 2 Form; Interchangable information (1 Viewer)

[evictme]

Hello All,

I have an employee/client database. A table for Employees and a Table for Clients.

I have a form for entering Client Info and Employee Info.

Each client is alloted specific amount of hours and assigned to an employee. each employee may have more than one client.

I want to see client info (name and hours) on the employee table/form and Assigned employee on the client info table/forms. So if i change the employee assigned to the client on the client page it will update on the employee table/form as well.

I am currently stuck as to whether I need an extra table for the hours or how I go about linking both tables in order to accomplish this. Currently, the database has some information but since its new I am working on the structure so I am flexible in regards to remaking tables/forms.

I would really appreciate any help or if you would point me in the right direction for this....

Thank you in advance..

Cheers.

- e.vict

 

[jzwp22]

Welcome to the forum!

Each client is alloted specific amount of hours and assigned to an employee.

Can more than 1 employee be assigned to a client?

How do you currently record the time? by day?

Is the allocated time for a client by day, week, month?

 

[evictme]

yes, more than one employee can be assigned to the client.

client hours are entered by week. Its more of a reference than it is time keeping.

 

[jzwp22]

You will need a couple more tables

Assuming your client & employee tables look something like this:

tblClient
-pkClientID primary key, autonumber
-txtClientName

tblEmployee
-pkEmpID primary key, autonumber
-txtFName
-txtLName

Now the new tables

tblClientEmployees
-pkClientEmpID primary key, autonumber
-fkClientID foreign key to tblClient
-fkEmpID foreign key to tblEmployee

tblClientEmpHours
-pkClientEmpHoursID primary key, autonumber
-fkClientEmpID foreign key to tblClientEmployees
-dtePeriodEnding (ending date of the period)
-spHours

 

[evictme]

Thanks Jzwp22,

One thing im not getting, I've never worked with "Foreign Keys" before does this mean that I create them as the name of a field/column in the table and set the relationship between it and the source? or is this another feature entirely?

Also, I guess I would like to know how to incorporate these tables so they're functional within the forms or overall.

I apologize for my ignorance, i have created Access dBases before but never one this complex.

Thank you in advance.

 

[jzwp22]

One thing im not getting, I've never worked with "Foreign Keys" before does this mean that I create them as the name of a field/column in the table and set the relationship between it and the source?

Yes, the primary key field of one table relates to the foreign key field of another table thus forming the one(primary)-to-many (foreign) relationship between the two tables. The autonumber datatype used for the primary key as I have shown previously is essentially a special case of a long number integer. So to make a join correctly, the foreign key field must be a long number integer datatype.

Also, I guess I would like to know how to incorporate these tables so they're functional within the forms or overall.

With respect to forms, you would have a main form that is based on the table that makes up the one side of the one-to-many relationship and within that form you would have a subform. That subform would be based on the table that makes up the many side of the relationship.

If you create the tables and then set up the relationships in the relationship window, Access will automatically link the main and subforms when you add the subform to the main form.

As a caution, I would not work on forms until you have worked out all of the tables and relationships you will need for your application.