how search for a record in multiple tables

[Terkpeh1990]

hi
i have five tables namely
team a
team b
team c
team d
team e

a staff can find himself in all the teams .
is there a way that when i query the staff name , the system should be able to open a form showing that staff's name in all the team he is in .

need help please . thanks

 

[Gasman]

With the structure you have there, I think you would need to use a union query, that is the same query 5 times, just changing the table name?

I'd have a staff table, a team table and a junction table to link the two.
At a minimum have the team table with a field for team and have each staff member in the table multiple times. Then it would be one query that shows all the teams a staff member is in.

HTH

 

[isladogs]

Agree with Gasman
You are creating a spreadsheet like structure which doesn't work well in a database

Suggest you read up on normalisation

 

[Pat Hartman]

Take a step back and think about what you would have to do if next year you add a sixth team? It won't be a pretty sight.

If you were CitiBank, would you have a separate table for each customer to manage their transactions? Would you have a separate table for each branch office to manage the local employees? The answer is NO a thousand times NO and for the same reason, you wouldn't have individual tables to manage each team.

 

[Terkpeh1990]

thanks guys. guess will have to restructure the tables again

 

[jdraw]

For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials from RogersAccessLibrary.
Class info system
Catering Business
Widgets