Updating information in the tables

[jamilian]

Hi

As part of a new management job, I've decided the best way to handle the huge amounts of information on the employees I manage is to use an Access database rather than the endless excel files that were previously used by my predecessor, requiring too much time to update.

So, I've created a database with the following tables to link one employee to the projects s/he works on, any issues/problems noted with the employee, inventory of equipment s/he has, training s/he follows and his/her’s salary:

Tables:
1. Contact Details
2. Projects
3. Issues
4. Inventory
5. Training
6. Salary

Each table has a primary key, e.g. Contact Details ID, Projects ID, etc etc.



I also created a one-to-many relationship between Contact Details (unique to each person) and each respective table.

Table: Contact Details.
Contact Details ID - AutoNumber
Projects ID - Number
Issues ID - Number
Inventory ID - Number
Training ID - Number
Salary ID – Number
…and the other fields in this table, i.e. address, phone number etc.

I tried creating a relationship between each ID above and its respective table that I also created.

The form I created is a tabbed form with a subform in it.
For example:

The Contact Details Form (pop up) currently has 4 tabs where I use fields from the Contact Details Table in the first three tabs (no problem there) and the fourth tab has a sub form in it with the Training Table.
The idea is to have a pop-up form where I can quickly access information about an individual employee but shifting through the relevant tabs.

However, I have several problems.

1. When I try to create all the one-to-many relationships from the table Contact Details to each table I get the error message that there’s too many indexes.
2. When I enter data for a contact into the Contact Table and then enter training information into the Training Table, I cannot link it to that unique person, because it links it to all the people I have entered into the database, e.g. I enter all the contact details for person1, 2, 3, 4, 5 etc and in the Training Table, training1, 2, 3, 4, 5 etc. However, it links all trainings to all people!!

I’ve been reading a lot as I’ve started from scratch…this is all new to me and thought I had understood the basics to get the database up and running. However, I think I must be missing some basic design elements that I’ve overlooked.

Can anyone throw some light on what I need to do to make this work? Also, I need to make this scalable. As my job progresses, I may wish to add more tables to link to the individual employee.

[Jamy]

1) Are you storing the contact ID (or name) in each table?
2) I know it sounds weird, as many people don't suggest this, however try using allow multiple values (this cannot be undone, so make a backup before you thy it). You will be able to add multiple contacts to the tables(trainings). Then use the child record contactid.value to get the form right.

Hope this helps.