By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

Summing columns in two separate queries

P: n/a
I have two queries each of which having a column that I want to sum,
and then add the two sums together. When I sum one query or the
other, I get reasonable numbers. But when I try to add the two sums
together I'm getting a ridiculously high number.

Below is my SQL line that is trying to add together the colomns of
qryDetlPALIncome and qryDetlPALIncomeNoInvoice.

SELECT Sum(qryDetlPALIncomeNoInvoice.DepositAmt)+Sum(qryD etlPALIncome.Amount)
FROM qryDetlPALIncome, qryDetlPALIncomeNoInvoice;

Thanks,
Rich
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Richard Coutts" <rc*****@comcast.net> wrote in message
news:70**************************@posting.google.c om...
I have two queries each of which having a column that I want to sum,
and then add the two sums together. When I sum one query or the
other, I get reasonable numbers. But when I try to add the two sums
together I'm getting a ridiculously high number.

Below is my SQL line that is trying to add together the colomns of
qryDetlPALIncome and qryDetlPALIncomeNoInvoice.

SELECT Sum(qryDetlPALIncomeNoInvoice.DepositAmt)+Sum(qryD etlPALIncome.Amount) FROM qryDetlPALIncome, qryDetlPALIncomeNoInvoice;


Post your table structures with some sample data and the output you need and
someone will be able to help.
Nov 13 '05 #2

P: n/a
>> SELECT
Sum(qryDetlPALIncomeNoInvoice.DepositAmt)+Sum(qryD etlPALIncome.Amount)
FROM qryDetlPALIncome, qryDetlPALIncomeNoInvoice;


Post your table structures with some sample data and the output you need and
someone will be able to help.


The tables are pretty straight forward with the two fields being
summed being of type 'Currency.' E.g., if
qryDetlPALIncomeNoInvoice.DepositAmt has the values
100.00
200.00
and qryDetlPALIncome.Amount has the values
25.00
35.00
50.00
then
SELECT Sum(qryDetlPALIncomeNoInvoice.DepositAmt)
FROM qryDetlPALIncomeNoInvoice;
returns 300.00 and
SELECT Sum(qryDetlPALIncome.Amount)
FROM qryDetlPALIncome;
returns 110.00 but
SELECT Sum(qryDetlPALIncomeNoInvoice.DepositAmt)+Sum(qryD etlPALIncome.Amount)
FROM qryDetlPALIncome, qryDetlPALIncomeNoInvoice;
returns 1120.00. I'm still a newbie and I'm sure I'm missing
something simple. I could break out the two sums as separate saved
queries and then sum their results together, but I'd rather not if
possible.

Thanks,
Rich
Nov 13 '05 #3

P: n/a
"Richard Coutts" <rc*****@comcast.net> wrote in message
news:70**************************@posting.google.c om...
SELECT Sum(qryDetlPALIncomeNoInvoice.DepositAmt)
FROM qryDetlPALIncomeNoInvoice;
returns 300.00 and
SELECT Sum(qryDetlPALIncome.Amount)
FROM qryDetlPALIncome;
returns 110.00 but
SELECT Sum(qryDetlPALIncomeNoInvoice.DepositAmt)+Sum(qryD etlPALIncome.Amount) FROM qryDetlPALIncome, qryDetlPALIncomeNoInvoice;

You have what is known as a cartesian product; your result being the sum of
all combinations of rows in the two queries.
Try this instead:

select sum(Amount) +
(
select sum(DepositAmt)
from qryDetlPALIncomeNoInvoice
)
from qryDetlPALIncome


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.