how search for a record in multiple tables (1 Viewer)

Terkpeh1990 · Dec 6, 2017

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 · Dec 6, 2017

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 · Dec 6, 2017

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 · Dec 6, 2017

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 · Dec 7, 2017

thanks guys. guess will have to restructure the tables again

jdraw · Dec 7, 2017

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

how search for a record in multiple tables (1 Viewer)

Terkpeh1990

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Terkpeh1990

Registered User.

jdraw

Super Moderator

Similar threads

Users who are viewing this thread