SQL command in a search form.

[agh5147]

Hello,



In Access 2013, I am trying to get multiple tables, specifically tbl alpha, tbl bravo, and tbl Charlie to be joined under one field. For example, I have a search for with these fields( empid, first name, last name, team name training name). This search form is to allow a employees manager to go into the database search an employee by first name, last name, empid , or even training, and return back the all information on the employee. I have been able to search by empid, team name, first name and last name, but I cant seem to get the trainings to work. I want one field in the search form to pull back from all the teams to see what trainings that employee on that specific team completed. I cant seem to get this to work. In the query design for the search form I didn't know if it was possible to put all three teams in on column field instead of having a field for alpha, bravo, and Charlie.

Things to note-
1. The employee Id is a combo of number and letters
2. each employee is only assigned to a single team.
3. Teams may have different trainings from one another.

I don't know if this problem can be fixed without going in and changing the sql statements or if there is a way to combine the 3 different teams in one field.

[June7]

Those are your real table names? You have 3 teams and each has its own table? Should be one table with another field for teamID.

[rpeare]

He posted on another thread and about 3 people pointed out the structure needed to be changed

[agh5147]

June 7 I am not understanding could you elaborate

[June7]

So this is essentially a duplicate of https://www.accessforums.net/showthread.php?t=66631. If you don't restructure db to have one table of data for all teams, then use a UNION query to emulate the same and then use the UNION query in other queries just as you would a table, except cannot edit data via the UNION.

Apply basic relational database normalization concept.