Summing columns in two separate queries

[Richard Coutts]

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

[John Winterbottom]

"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.

[Richard Coutts]

>> 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

[John Winterbottom]

"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