Update two tables at the same time

[bmacias]

Hi all

I have one table with basic employee information. I also have a table in which I have the employee's favorite soccer team. How do I do it so that when I enter an employee name in the basic employee information table a record with this new employee is autimatically added to the soccer table?

Then I would only have to go to the soccer table and put his/her soccer team instead of having to retype the employees name.

You help is appreciated.

 

[Corticus]

Hi bmacias,

You need to take advantage of the relational nature of Access tables.

I have one table with basic employee information.

This should be set up something like:
EmpID(autonumber)/EmpName(text)/EmpData(whatever)

I also have a table in which I have the employee's favorite soccer team.

This table should be set up like:
SoccerTeamID(autonumber)/TeamName(text)/EmpID(number)

How do I do it so that when I enter an employee name in the basic employee information table a record with this new employee is autimatically added to the soccer table?

From Tools|Relationships, add these two tables, and drag over the field "EmpID' from the emplyee table to the same field on the team table. Check 'enforce referential integrity' and 'cascade update related records'.

Now you data is normalized and more suited for a database.

HTH,

 

[bmacias]

Thanks Curticus.

Unfortunately, this solution only works only for data already entered. That is, if I change the employee number that it updates it in the soccer field. But if I enter a brand new empoyee this does not get updated in the soccer table.

Is there anything else I could be doing wrong?

Thanks again.

Ben

 

[Corticus]

I don't quite follow,

If you add a new employee, autonumber will add an ID for them. No records in the team table will be related until you put them there. With cascading updates, if you change an employees ID in the employee table, any related records in other tables will also be updated to reflect this new employee ID.

Does that help any more?

 

[bmacias]

Sorry for not explaining myself better.

When the autonumber (the unique identifier) is created in the employee table. Can it be replicated automatically in the soccer table?

Thus eliminating the need to have to type it again.

Again, please pardon my inability to put forth my dilema in a consice clear-cut way.

Ben

 

[Corticus]

When the autonumber (the unique identifier) is created in the employee table. Can it be replicated automatically in the soccer table?

It shoudln't be. You have to know with what teams in the soccer table to associate with players from the players table.

When you say automatically replicated it screams "Select Query".

If you have data like
PlayerID/Player
1/Joe
2/Fred
3/Bob
4/Gunthar

then your team table would be
TeamID/Team/Player
1/A team/1
1/A team/2
1/A team/3

How would this table know that all three players are on team 1? It couldn't automatically fill them in because you have to decide how they are related.

I think the answer to the question that I haven't figured out yet will be a select query. Why don't you show me what data you have, and what you want the data to look like.

Sorry I'm missing the point here...

-Cort

 

[Russell Hauf]

The short answer is to add a field to your Employee table in which you can put their favorite soccer team. Is there a reason that you want to have it in a different table? If you are importing the data, you can run an update query that updates the "Favorite Team" field (after you add it to the table, of course).

HTH,

Russell