Subtract results from two queries?

[clintonb]

Here is a type of problem that has plagued me for way too long.

Let's say we have an account charge table that holds charges for an
account.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00


Let's also say we have an account payment table that holds payments
for an account.

Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00


How can I write a query that will give the balance for each account?
Account Number Balance
123 $225.25
548 $0.00

I'd like to somehow sum up the charges, sum up the payments and
subtract them using a single query. Right now I'm forced to write a
view to sum up the charges and write a second view to sum up the
payments, and then use a join query to join the views to do the
subtraction.

I'm assuming this must be a common problem that others have worked out
before.

Any help would be greatly appreciated.
Thanks!
- Clint

 

[FJC]

This is quite easy...and I am not a programmer.

Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:

SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;

 

[John Spencer]

SQL for the query would look like the following

SELECT AccountCharge.AccountNumber
, Sum(AccountCharge.Charge) as TotalCharges
, Sum(AccountPayment.Payment) as TotalPayments
, Sum(AccountCharge.Charge) - Nz( Sum(AccountPayment.Payment) ,0) as Balance
FROM AccountCharge LEFT JOIN AccountPayment
ON AccountCharge.AccountNumber = AccountPayment.AccountNumber
GROUP BY AccountCharge.AccountNumber

If you can't copy and paste that into the SQL view of a query and can only
build a query in design view (with the grid), post back for instructions

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This is quite easy...and I am not a programmer.

Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:

SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;

Here is a type of problem that has plagued me for way too long.

Let's say we have an account charge table that holds charges for an
account.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00


Let's also say we have an account payment table that holds payments
for an account.

Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00


How can I write a query that will give the balance for each account?
Account Number Balance
123 $225.25
548 $0.00

I'd like to somehow sum up the charges, sum up the payments and
subtract them using a single query. Right now I'm forced to write a
view to sum up the charges and write a second view to sum up the
payments, and then use a join query to join the views to do the
subtraction.

I'm assuming this must be a common problem that others have worked out
before.

Any help would be greatly appreciated.
Thanks!
- Clint

 

[clintonb]

Thanks for the reply FJC. But I wasn't looking for an implementation
of the last query I described in words only. I was trying to figure
out how to sum up the charges and payments and subtract the two using
only one query.

- Clint

This is quite easy...and I am not a programmer.

Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:

SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;

Here is a type of problem that has plagued me for way too long.

Let's say we have an account charge table that holds charges for an
account.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00

Let's also say we have an account payment table that holds payments
for an account.

Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00

How can I write a query that will give the balance for each account?
Account Number Balance
123 $225.25
548 $0.00

I'd like to somehow sum up the charges, sum up the payments and
subtract them using a single query. Right now I'm forced to write a
view to sum up the charges and write a second view to sum up the
payments, and then use a join query to join the views to do the
subtraction.

I'm assuming this must be a common problem that others have worked out
before.

Any help would be greatly appreciated.
Thanks!
- Clint

 

[clintonb]

Thanks.

I wonder how I would do this in another database such as Microsoft SQL
Server?
Would it have an equivalent function for the Access NZ function that
would treat a null payment sum as zero?
Hmmm....

- Clint

SQL for the query would look like the following

SELECT AccountCharge.AccountNumber
, Sum(AccountCharge.Charge) as TotalCharges
, Sum(AccountPayment.Payment) as TotalPayments
, Sum(AccountCharge.Charge) - Nz( Sum(AccountPayment.Payment) ,0) as Balance
FROM AccountCharge LEFT JOIN AccountPayment
ON AccountCharge.AccountNumber = AccountPayment.AccountNumber
GROUP BY AccountCharge.AccountNumber

If you can't copy and paste that into the SQL view of a query and can only
build a query in design view (with the grid), post back for instructions

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This is quite easy...and I am not a programmer.

Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:

SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;

"clintonb" wrote:

 

[clintonb]

John,

Are you sure your query works?
I tried something similar to this in SQL Server (I didn't get to try
it in Access yet), and I noticed that if the left side of the join has
more records than the right side of the join, the right side entries
are summed up multiple times.

So for the data given below, your query would return something like:
AccountNumber TotalCharges TotalPayments Balance
123 325.25 200.00 125.25
548 86.00 40.00
46.00

The single $100.00 payment for account number 123 was added twice,
once for each charge for account number 123.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00


Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00

- Clint

SQL for the query would look like the following

SELECT AccountCharge.AccountNumber
, Sum(AccountCharge.Charge) as TotalCharges
, Sum(AccountPayment.Payment) as TotalPayments
, Sum(AccountCharge.Charge) - Nz( Sum(AccountPayment.Payment) ,0) as Balance
FROM AccountCharge LEFT JOIN AccountPayment
ON AccountCharge.AccountNumber = AccountPayment.AccountNumber
GROUP BY AccountCharge.AccountNumber

If you can't copy and paste that into the SQL view of a query and can only
build a query in design view (with the grid), post back for instructions

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This is quite easy...and I am not a programmer.

Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:

SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;

"clintonb" wrote:

 

[John Spencer]

Whoops, you are right. It should have been more like the following.

SELECT A.AccountNumber,
, TotalCharges
, TotalPayments
, TotalCharges - NZ(TotalPayments) as Balance
FROM
(Select AccountNumber , Sum(Charge) as TotalCharges
FROM AccountCharge
GROUP BY AccountNumber) as A
LEFT JOIN
(Select AccountNumber, Sum(Payment) as TotalPayments
FROM AccountPayment
GROUP BY AccountNumber) As B
ON A.AccountNumber = B.AccountNumber

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Are you sure your query works?
I tried something similar to this in SQL Server (I didn't get to try
it in Access yet), and I noticed that if the left side of the join has
more records than the right side of the join, the right side entries
are summed up multiple times.

So for the data given below, your query would return something like:
AccountNumber TotalCharges TotalPayments Balance
123 325.25 200.00 125.25
548 86.00 40.00
46.00

The single $100.00 payment for account number 123 was added twice,
once for each charge for account number 123.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00


Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00

- Clint

SQL for the query would look like the following

SELECT AccountCharge.AccountNumber
, Sum(AccountCharge.Charge) as TotalCharges
, Sum(AccountPayment.Payment) as TotalPayments
, Sum(AccountCharge.Charge) - Nz( Sum(AccountPayment.Payment) ,0) as Balance
FROM AccountCharge LEFT JOIN AccountPayment
ON AccountCharge.AccountNumber = AccountPayment.AccountNumber
GROUP BY AccountCharge.AccountNumber

If you can't copy and paste that into the SQL view of a query and can only
build a query in design view (with the grid), post back for instructions

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This is quite easy...and I am not a programmer.
Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:
SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;
:
Here is a type of problem that has plagued me for way too long.
Let's say we have an account charge table that holds charges for an
account.
Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00
Let's also say we have an account payment table that holds payments
for an account.
Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00
How can I write a query that will give the balance for each account?
Account Number Balance
123 $225.25
548 $0.00
I'd like to somehow sum up the charges, sum up the payments and
subtract them using a single query. Right now I'm forced to write a
view to sum up the charges and write a second view to sum up the
payments, and then use a join query to join the views to do the
subtraction.
I'm assuming this must be a common problem that others have worked out
before.
Any help would be greatly appreciated.
Thanks!
- Clint

 

[John Spencer]

DANG NAB IT. Forgot the second argument in the NZ function.


SELECT A.AccountNumber,
, TotalCharges
, TotalPayments
, TotalCharges - NZ(TotalPayments,0) as Balance
FROM
(Select AccountNumber , Sum(Charge) as TotalCharges
FROM AccountCharge
GROUP BY AccountNumber) as A
LEFT JOIN
(Select AccountNumber, Sum(Payment) as TotalPayments
FROM AccountPayment
GROUP BY AccountNumber) As B
ON A.AccountNumber = B.AccountNumber

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Whoops, you are right. It should have been more like the following.

SELECT A.AccountNumber,
, TotalCharges
, TotalPayments
, TotalCharges - NZ(TotalPayments) as Balance
FROM
(Select AccountNumber , Sum(Charge) as TotalCharges
FROM AccountCharge
GROUP BY AccountNumber) as A
LEFT JOIN
(Select AccountNumber, Sum(Payment) as TotalPayments
FROM AccountPayment
GROUP BY AccountNumber) As B
ON A.AccountNumber = B.AccountNumber

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Are you sure your query works?
I tried something similar to this in SQL Server (I didn't get to try
it in Access yet), and I noticed that if the left side of the join has
more records than the right side of the join, the right side entries
are summed up multiple times.

So for the data given below, your query would return something like:
AccountNumber TotalCharges TotalPayments Balance
123 325.25 200.00 125.25
548 86.00 40.00
46.00

The single $100.00 payment for account number 123 was added twice,
once for each charge for account number 123.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00


Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00

- Clint

SQL for the query would look like the following

SELECT AccountCharge.AccountNumber
, Sum(AccountCharge.Charge) as TotalCharges
, Sum(AccountPayment.Payment) as TotalPayments
, Sum(AccountCharge.Charge) - Nz( Sum(AccountPayment.Payment) ,0) as
Balance
FROM AccountCharge LEFT JOIN AccountPayment
ON AccountCharge.AccountNumber = AccountPayment.AccountNumber
GROUP BY AccountCharge.AccountNumber

If you can't copy and paste that into the SQL view of a query and can
only
build a query in design view (with the grid), post back for instructions

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.



This is quite easy...and I am not a programmer.
Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group
on
Queries. The SQL would look something like this for the last Q:
SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group
on
Pmt Table].Field1 = [Group On Charge Table].Field1;
:
Here is a type of problem that has plagued me for way too long.
Let's say we have an account charge table that holds charges for an
account.
Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00
Let's also say we have an account payment table that holds payments
for an account.
Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00
How can I write a query that will give the balance for each account?
Account Number Balance
123 $225.25
548 $0.00
I'd like to somehow sum up the charges, sum up the payments and
subtract them using a single query. Right now I'm forced to write a
view to sum up the charges and write a second view to sum up the
payments, and then use a join query to join the views to do the
subtraction.
I'm assuming this must be a common problem that others have worked out
before.
Any help would be greatly appreciated.
Thanks!
- Clint

 

[clintonb]

John,

This query works. Thank you very much. I didn't know you could give
a subquery an alias. That's a handy trick to know.

- Clint

Whoops, you are right. It should have been more like the following.

SELECT A.AccountNumber,
, TotalCharges
, TotalPayments
, TotalCharges - NZ(TotalPayments) as Balance
FROM
(Select AccountNumber , Sum(Charge) as TotalCharges
FROM AccountCharge
GROUP BY AccountNumber) as A
LEFT JOIN
(Select AccountNumber, Sum(Payment) as TotalPayments
FROM AccountPayment
GROUP BY AccountNumber) As B
ON A.AccountNumber = B.AccountNumber

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Are you sure your query works?
I tried something similar to this in SQL Server (I didn't get to try
it in Access yet), and I noticed that if the left side of the join has
more records than the right side of the join, the right side entries
are summed up multiple times.

So for the data given below, your query would return something like:
AccountNumber TotalCharges TotalPayments Balance
123 325.25 200.00 125.25
548 86.00 40.00
46.00

The single $100.00 payment for account number 123 was added twice,
once for each charge for account number 123.

Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00

Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00

- Clint

SQL for the query would look like the following
SELECT AccountCharge.AccountNumber
, Sum(AccountCharge.Charge) as TotalCharges
, Sum(AccountPayment.Payment) as TotalPayments
, Sum(AccountCharge.Charge) - Nz( Sum(AccountPayment.Payment) ,0) as Balance
FROM AccountCharge LEFT JOIN AccountPayment
ON AccountCharge.AccountNumber = AccountPayment.AccountNumber
GROUP BY AccountCharge.AccountNumber
If you can't copy and paste that into the SQL view of a query and can only
build a query in design view (with the grid), post back for instructions
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This is quite easy...and I am not a programmer.
Write a query to group on and sum for both your paymnent Table and your
charge Table. Then write another query to combine both of your Group on
Queries. The SQL would look something like this for the last Q:
SELECT [Group on Pmt Table].Field1, [Group On Charge
Table]![SumOfField2]+[Group on Pmt Table]![SumOfField2] AS Expr1
FROM [Group on Pmt Table] INNER JOIN [Group On Charge Table] ON [Group on
Pmt Table].Field1 = [Group On Charge Table].Field1;
:
Here is a type of problem that has plagued me for way too long.
Let's say we have an account charge table that holds charges for an
account.
Table: AccountCharge
AccountNumber Charge
123 $125.00
123 $200.25
548 $86.00
Let's also say we have an account payment table that holds payments
for an account.
Table: AccountPayment
AccountNumber Payment
123 $100.00
548 $40.00
548 $46.00
How can I write a query that will give the balance for each account?
Account Number Balance
123 $225.25
548 $0.00
I'd like to somehow sum up the charges, sum up the payments and
subtract them using a single query. Right now I'm forced to write a
view to sum up the charges and write a second view to sum up the
payments, and then use a join query to join the views to do the
subtraction.
I'm assuming this must be a common problem that others have worked out
before.
Any help would be greatly appreciated.
Thanks!
- Clint