Two become one

At work I have created a training database that records all the latest Health & Safety work stuff. It consists of one table: MainData1. In it I have a list of all my employees and whether they have passed training or not.

I have now developed an Admin database that stores all my employees personal information. This is also on one table: MainData2.

I plan to use the second database as the main one.

The problem I have now is that when I add names to MainData2, I then have to go into MainData1 and manually add the names to keep it up to date.

I am aware of the relationships thing but after hours of trying to link them so MainData2 updates the other, well I just failed badly.

Any suggestions?

Thanks in advance forum!

Fallyhag

MainData2 Fields:
EmplyeeID (PK), FName, LName, Etc

MainDate1 Fields
TableID (PK), EmployeeID(FK), etc (Employee Personnal Info not necesary, in MainData2)

In the RelationShip toolbar button (Relationship Dialoge Box) set up relationships, select the two tables, click & Drag employeeID, from Maindata2 to Maindata1 and click on "enfore referencial integrity". This forces you to have a record in the Maindata2 before you can have a record in Maindata1

save changes

With this done, when you go to add a new record in MainData1 table, you need to be able to select the employee from MainData1 to get the employeeID (or know the employeeID before hand).

If there is a one to one relationship between tables you can make the employeeID the PK and FK in the MainData1 table.

Hope this is clear

S-

Here is what I have done:

1. I have created a folder and put both my databases in this folder.

2. I have then gone to MainData2 and selected "get external data" and imported MainData1 into MainData2.

3. I then followed your instructions:

In the RelationShip toolbar button (Relationship Dialoge Box) set up relationships, select the two tables, click & Drag employeeID, from Maindata2 to Maindata1 and click on "enfore referencial integrity". This forces you to have a record in the Maindata2 before you can have a record in Maindata1

4. I then opened MainData1 but none of the new employees had been added? I tried the refresh but nothing.

5. here is a connection coz I can carry out queries now but why isnt my new employees showing up on MainData2?



Fallyhag

With relationship the information is not autmatically ADDED, it allows links for reference purposes.

For example:

Customer Table:
CID, CNAME, CADDRESS
23, John Doe, 123 german street

Order Table:
ONum, CID, OSHIPADDRESS
45, 23, 345 swiss street

Execute following SQL

Select Customer.CNAME, Customer.CADDRESS, Order.ONUM, Order.OSHIPADDRESS FROM Customer InnerJoin Order ON Customer.CID = Order.CID

results
John Doe, 123 german street, 45, 345 swiss street



What relationships let you do is relate to info already in the DB so you don't have to duplcate everying. The this example you didn't need to have Customer name in more then one table.

See pervious comments:
"With this done, when you go to add a new record in MainData1 table, you need to be able to select the employee from MainData2 to get the employeeID (or know the employeeID before hand)."

When you want information to show up in Maindata1 you need to add the record just like normal, but instead of adding employee name and additional info, you just add the Employee ID and the relationships will allow you to display the other information when you query or create a report of information in MainData1.

Does this help clarify?

S-

Yep, sure does!

Now I have had a sit down and think about it I kind of make sense of it all now.

I think I have a bit of redesigning to do eh!?

Thanks for clearing that up for me Sbaxter, you are a star!

Fallyhag

Eh, Where are you from?

S-

Originally from the South of England but currently living in Germany (Near Dortmund).

Whilst I am typing again:

How would you create this database?

As it is, I have one huge table which is nearly running out of space and another small table to record their training records.

Common sense says that a huge table full of info isnt very efficient and in practice I think I should have divided all the info up into seperate table - But I have no confidence in the Relationships subject.

My ideal database would be where I could just have lots of tables that were all interlinked and all update when I enter info into just the main form.

I thought I was on the right track with the two tables I had but after reading your 2nd to last, well I am way off.

May I ask for your approach?

Fallyhag

Germany! Did you pick up Eh, there or is Wales, I'm orginally from Canada, but live in the United Stated now (wife didn't want to lice in Canada o'well)

Yea I would be glad to help you. Do you have MS Visio?. I will email you later and give you my email address so we can take this discussion Offline (don't know if you have confidentail info to worry about), plus I can send you other infromation that won't fit this forumn size limitation.

S-

Yep, I have got MS Visio (Thanks to P2P) Naughty but cheap

Many thanks for the offer, appreciated!