Trying to combine four identical tables in one query

[CanadianAccessUser]

Hello,

I have multiple databases (one for each department). All 4 databases have an Agents table (tblAgents). Now I am creating another database that uses all of the agents on site. What I want to do is link all the agents tables and combine the values in one query.

Fields are:
UserID (PK)
AgentName
BadgeNumber (Employee Number)
Campaign (Department)
Coach (Supervisor)
HireDate
Status (Active / Leave of absense / Terminated)

The tables are linked to my new database. So logically, I'd think my next step is to query the tables. This is where I'm stuck.
How do I say:
Agent = tblAgentsDepartment1.AgentName and also tblAgentsDepartment2.AgentName and also tblAgentsDepartment3.AgentName and also tblAgentsDepartment4.AgentName?
I'll have to do this for each field involved...

Thanks in advance for any tips and tricks that might come my way.
Canadian

 

[SmallTomato]

If you are wanting to combine all the tables into one you need to look into a Union query.

Select x.1,x.2,x.3
From x
Union all
Select y.1, y.2,y.3
From y

Actually rereading your question, I am not 100% sure what you are trying to do. Do you have 3 structurally identical tables with all unique information you're trying to combine into one query (if so you need a union)? Or are you trying to link like agents with like agents from each table?

 

[jdraw]

I'm not clear of what you have either. If you have 4 tables with exactly the same structure, then you may have a design problem. But we don't understand your set up to offer moe focused responses.

 

[CanadianAccessUser]

Small Tomato was right. I'll try a Union and let you know.

jdraw:
I know it looks like a design problem, but where each of the identical tables come from different databases please tell me I didn't do this wrong...

Each department has the exact same info for each agent in their own database. I need a list of all agents from all departments with that exact same data. If an employee gets fired, for example, the supervisor updates the database for their department. If I link this db to all the tables in the other dbs I can have an up to date list to work from in this site wide db.

Makes sense eh?
I hope so, I'm going to try the union now...

 

[SmallTomato]

Why don't they all use the same database with a list of all employees to begin with?

But yes, you will need a union.. if you have trouble with it post back with what you came up with and I will help

 

[jdraw]

I would be looking at expanding the meaning of your database. Why does each department have their own, if the data is important to all departments? You'll have multiple similar tables, that will constantly need reconciliation. And how will you o anyone know which version is correct?

You might consider a more corporate approach. Get a list of agents(or whatever) into an "authoritative table/database". That's the Mother! It is the authority.
If you still need departmental databases for whatever reason, then link to the authoritative table/database for corporate subjects. But don't have conflicting versions of the "truth" by design.

 

[SmallTomato]

<snip because I ramble then change my mind half way through>

On a side note, don't you want an End Date/Term Date? That way you can better track rehires/how long they worked in the company/for each department?

 

[CanadianAccessUser]

Jdraw: Eventually a corporate approach may be the answer, but the departments still need their own databases because it is only the agents table that is common. Each department has many requirements within their own database. Some are similar, but only tblAgents is exactly the same as the other departments.
Also, the supervisors would not be working in the corporate db, only the managment team. As it is the supervisor's job to term an employee, it needs to be done in their db, then linked to the site wide one that I'm building now. (The site wide one really does nothing but track "Redzones" which is the potential loss of an employee for reasons like Pregnancy, Pay Rate, Benefits, scheduling issues, etc.) Once I get it all built and working, I'll look at my options. Luckily I've built it all so that it's just a matter of linking tables one way or the other.

SmallTomato: These databases are more for the supervisors to track Agent stats and such. HR has their own tools for tracking rehires etc. Terminating an employee in the departmental databases and my new site wide one really only removes them from combo box value lists and weekly/monthly statistics reports.

BTW... The union worked great. I can't believe I forgot about it.
Just call me newbie, eh

Canadian

 

[SmallTomato]

Okay, glad you got it worked out. I do not know how everything is structured and you prob already do this for your reports, but make sure you take into account that an employee might be terminated in one database and active in another when they transfer departments.

Good luck!

 

[CanadianAccessUser]

Thanks SmallTomato!

Transfers are treated a little differently, but yes, I may end up with two records for the same person. Thing is, if the transfer from dept has an agent status of "Transfer" then they won't show up in the site wide database under that department at all so it will work fine for what I'm doing... at this point... Thanks for the tip!

Canadian