Combining Queries

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

I use these 2 queries...

SELECT BTA_Trades.FirmId, Sum(BTA_Trades.TradeVolume) AS MM_NonPIP
FROM BTA_Trades
WHERE BTA_Trades.AccountType='MarketMaker' And
BTA_Trades.TradeType<>'Pip'
GROUP BY BTA_Trades.FirmId;

SELECT BTA_Trades.CounterpartFirmId, Sum(BTA_Trades.TradeVolume) AS
PA_OUT
FROM BTA_Trades
WHERE (((BTA_Trades.AccountNumber)='PA-OUT'))
GROUP BY BTA_Trades.CounterpartFirmId;

Is there a way to combine the queries so I don;t have to run each of
them ?

Thank You in Advance.
 
carl said:
I use these 2 queries...

SELECT BTA_Trades.FirmId, Sum(BTA_Trades.TradeVolume) AS MM_NonPIP
FROM BTA_Trades
WHERE BTA_Trades.AccountType='MarketMaker' And
BTA_Trades.TradeType<>'Pip'
GROUP BY BTA_Trades.FirmId;

SELECT BTA_Trades.CounterpartFirmId, Sum(BTA_Trades.TradeVolume) AS
PA_OUT
FROM BTA_Trades
WHERE (((BTA_Trades.AccountNumber)='PA-OUT'))
GROUP BY BTA_Trades.CounterpartFirmId;

Is there a way to combine the queries so I don;t have to run each of
them ?

Thank You in Advance.
Click to expand...

"Combine"? This word can be interpreted in several different way. What do
you want the resulting query to return? A little more information please.
Show us a couple rows of sample data returned from each query, then show us
what you want the "combined" results to look like.
 
I use these 2 queries...

SELECT BTA_Trades.FirmId, Sum(BTA_Trades.TradeVolume) AS MM_NonPIP
FROM BTA_Trades
WHERE BTA_Trades.AccountType='MarketMaker' And
BTA_Trades.TradeType<>'Pip'
GROUP BY BTA_Trades.FirmId;

SELECT BTA_Trades.CounterpartFirmId, Sum(BTA_Trades.TradeVolume) AS
PA_OUT
FROM BTA_Trades
WHERE (((BTA_Trades.AccountNumber)='PA-OUT'))
GROUP BY BTA_Trades.CounterpartFirmId;

Is there a way to combine the queries so I don;t have to run each of
them ?

Thank You in Advance.
Click to expand...

That are you doing with the queries? Do you just want to see a datasheet with
te results?

If you just want a datasheet combining the results from the two queries, one
after the other, use a UNION query:

SELECT BTA_Trades.FirmId, Sum(BTA_Trades.TradeVolume) AS MM_NonPIP
FROM BTA_Trades
WHERE BTA_Trades.AccountType='MarketMaker' And
BTA_Trades.TradeType<>'Pip'
GROUP BY BTA_Trades.FirmId
UNION ALL
SELECT BTA_Trades.CounterpartFirmId, Sum(BTA_Trades.TradeVolume) AS
PA_OUT
FROM BTA_Trades
WHERE (((BTA_Trades.AccountNumber)='PA-OUT'))
GROUP BY BTA_Trades.CounterpartFirmId;

You can also base a report or form on this query (though the UNION query will
not be updateable; no UNION query, nor any Totals query, ever is).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
"Combine"?  This word can be interpreted in several different way. Whatdo
you want the resulting query to return? A little more information please.
Show us a couple rows of sample data returned from each query, then show us
what you want the "combined" results to look like.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for your help.


I am not too particular on how the output looks. Maybe like this ?


FirmId MM_NonPIP
BOX980 1996693
BOX549 1229560
BOX910 667716
BOX919 340773
BOX051 143669
BOX918 127853
BOX937 96074
BOX642 61667
BOX951 40521
BOX355 23533
BOX982 2598
BOX411 1506
BOX512 47

CounterpartFirmId PA_OUT
BOX501 23232
BOX792 7849
BOX411 4505
BOX916 2869
BOX982 1632
BOX938 1251
BOX983 1004
BOX060 743
BOX952 563
BOX226 499
BOX979 488
BOX123 394
BOX608 155
BOX512 88
BOX234 84
BOX953 72
BOX352 29
BOX050 26
BOX951 10
BOX074 10
BOX551 5
BOX445 1
 
carl said:
I am not too particular on how the output looks. Maybe like this ?


FirmId MM_NonPIP
BOX980 1996693
Click to expand...
CounterpartFirmId PA_OUT
BOX501 23232
Click to expand...
<snip>

I guess there is a failure to communicate. You seem to have shown some
sample rows from both queries, which is good.
Now, we need to know what the results would look like if "combined". I think
by "combined" you are asking for a single query to return both result sets
as a single resultset. So do you simply want them jumbled together so you
have no idea which query provided the data in the result? Like this?

FirmId MM_NonPIP
BOX501 23232
BOX980 1996693
BOX910 667716
BOX792 7849
BOX549 1229560
BOX411 4505

If so, John's union query suggestion is the way to go. if you want something
different, you need to tell us what that is.
 
I use these 2 queries...

SELECT BTA_Trades.FirmId, Sum(BTA_Trades.TradeVolume) AS MM_NonPIP
FROM BTA_Trades
WHERE BTA_Trades.AccountType='MarketMaker' And
BTA_Trades.TradeType<>'Pip'
GROUP BY BTA_Trades.FirmId;

SELECT BTA_Trades.CounterpartFirmId, Sum(BTA_Trades.TradeVolume) AS
PA_OUT
FROM BTA_Trades
WHERE (((BTA_Trades.AccountNumber)='PA-OUT'))
GROUP BY BTA_Trades.CounterpartFirmId;

Is there a way to combine the queries so I don;t have to run each of
them ?

Thank You in Advance.
Click to expand...

If you just want to display them on a Form, you could use two Subforms. If you
want to print them out, one after the other, with headers, use a Report with
two Subreports (or a report based on one, with a Subreport in the report
Header or Footer for the other).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top