Help linking Common Fields

[cooh23]

Good Afternoon All,

I am having a bit of a trouble looking for a way to link to tables.

Let's say I have table1 and table2

Both tables have the 50 U.S. States. The reason i have them named differently is because the 2 tables have different sources with thousands of records.

table1
- State Code
- Count of Agents
table2
- StateID - this links to a table called tblState to a field State
- Count of Agents


What i want to do is create a report that compares the two using the common field. e.g.:

State: CA (group State code and StateID)
- Count of agents (Table 1 + table 2 count of agents)

How can i tackle this? Is there an easier way of the way I want to happen?

Thank you,

 

[georgedwilkinson]

The SQL would be something like:

select State, sum([Count of agents]) as CountOfAgents
from
(select StateCode as State, [Count of agents] from table1
union
SELECT tblState.TheFieldInStateLikeTable1s as State, Table2.[Count of Agents]
FROM Table2 INNER JOIN tblState ON Table2.StateID = tblState.State)
group by State;

You didn't provide us with the field in tblState which has the "State" in it like the one in Table1 so I made up a name. Just replace that with the correct column name.