Merging queries

Dartagnan

Active Member
Joined
Jun 19, 2002
Messages
386
Is there a way to merge queries in Access? I have data from 2 different DB's and I need merge the data and present the data on 1 form.

One query will have an excel table which is then linked to a query from the 1st DB. This data represents all loans funded by an LO.

The 2nd query will be data straight from the 2nd DB. The data will be number of calls received by an LO by state. There will only be 3 columns of data.

I would like to combine the queries so the output shows the number of fundings in each state then the number of calls in each state.

Is this possible??
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Hi, I don't think you can purely merge queries (per your question) but you should be able to achieve what you want by linking the table(s) from one database into the other database and then building a new query. The new query will be based on the linked table plus any other information you want from the database. To link a table, open the database, click File -> Get External Data -> Link Tables and from there you should be able to work it out.

HTH, Andrew. :)
 

Dartagnan

Active Member
Joined
Jun 19, 2002
Messages
386
I think I will just have to run both queries independently and then merge through a pivot table.

Another question

I have a break down of each call an LO received in each state

Example:

Joe someone CA
Joe someone CA
Joe someone CA
Joe someone FL
Joe someone FL
Joe someone MN
Joe someone MN

How do I get Access to sum of the calls by state

Desired result:

Joe someone CA 3

Thank you
Joe someone FL 2
Joe someone MN 2
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Try using a total query (query design mode -> click view -> totals), group by state and count the incidences (i.e change "group by" to " count" for LO perhaps?).
HTH, Andrew :)
 

Dartagnan

Active Member
Joined
Jun 19, 2002
Messages
386

ADVERTISEMENT

That doesn't appear to work. What I need Access to do is similar to the SUMIF function in Excel. I need Access to sum the calls by state and give me the count.

Is that possible?

Thank you
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
When you say
That doesn't appear to work.
Are you seeing unexpected results or are you getting an error? If you post the SQL of what you have tried we should be able to decipher the problem.
Andrew
 

Dartagnan

Active Member
Joined
Jun 19, 2002
Messages
386
SELECT Count(lo_posid.Agent) AS CountOfAgent, session.STATE
FROM (org_level INNER JOIN [session] ON org_level.ORG_LEVEL_ID = session.OL03_ID) INNER JOIN lo_posid ON org_level.NAME = lo_posid.PosID
GROUP BY session.STATE, session.ACTION_TYPE_ID, session.CREATE_DATE
HAVING (((session.ACTION_TYPE_ID)=20) AND ((session.CREATE_DATE)>=#1/1/2005#));
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Try something like this :

SELECT session.STATE, lo_posid.Agent, Count(session.ACTION_TYPE_ID) AS CountOfACTION_TYPE_ID
FROM (org_level INNER JOIN [session] ON org_level.ORG_LEVEL_ID = session.OL03_ID) INNER JOIN lo_posid ON org_level.NAME = lo_posid.PosID
WHERE (((session.CREATE_DATE)>=#1/1/2005#))
GROUP BY session.STATE, lo_posid.Agent, session.ACTION_TYPE_ID
HAVING (((session.ACTION_TYPE_ID)="20"));

Be sure to check I have used the right variable and table names. The problem was the "group by" for the date field plus I think we should group by agent (assuming >1 agent / state) and count the events.

HTH, Andrew :)
 

Forum statistics

Threads
1,148,530
Messages
5,747,234
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top