Obtaining "Current Balance" from multiple "Amount" entries

[Such a Beginner]

I'm trying to design a database that will track my bank accounts and credit
cards. Thus far, everything works, except for my "Current Balance" queries.

I've designed the queries to sum each amount, and then set up a field for
Balance using the Expression:
Balance:[SumOfAmount4]-[SumOfAmount3]-[SumOfAmount2]-[SumOfAmount1], with
each Sum representing in order, Deposits, Checks, Debits, and CCPayments.

First, it only works if I have entered an amount in all four categories.
How do I make this work without having to enter an amount, and how do I set
the default to zero $?

Second, If I've made three deposits, one debit, three checks, and one CC
payment, the expression yields a balance that reflects three times the one
debit and three times the one CC Payment. In other words, it multiplies the
one debit via the sum function. How do I correct this?

 

[KARL DEWEY]

Use the Nz function like this ---
Balance:Nz([SumOfAmount4],0)-Nz([SumOfAmount3],0)-Nz([SumOfAmount2],0)-Nz([SumOfAmount1],0)
Post your SQL.
--
KARL DEWEY
Build a little - Test a little

I'm trying to design a database that will track my bank accounts and credit
cards. Thus far, everything works, except for my "Current Balance" queries.

I've designed the queries to sum each amount, and then set up a field for
Balance using the Expression:
Balance:[SumOfAmount4]-[SumOfAmount3]-[SumOfAmount2]-[SumOfAmount1], with
each Sum representing in order, Deposits, Checks, Debits, and CCPayments.

First, it only works if I have entered an amount in all four categories.
How do I make this work without having to enter an amount, and how do I set
the default to zero $?

Second, If I've made three deposits, one debit, three checks, and one CC
payment, the expression yields a balance that reflects three times the one
debit and three times the one CC Payment. In other words, it multiplies the
one debit via the sum function. How do I correct this?

 

[Klatuu]

I forgot a couple of other items. You can't join the tables because there is
not common field on which to join them. The only way to do that is with a
UNION query.
--
Dave Hargis, Microsoft Access MVP

Here is my SQL

SELECT qryArvestDeposits.SumOfAmount4, qryArvestCreditPayments.SumOfAmount3,
qryArvestChecks.SumOfAmount2, qryArvestDebits.SumOfAmount1,
[SumOfAmount4]-[SumOfAmount3]-[SumOfAmount2]-[SumOfAmount1] AS Balance
FROM qryArvestChecks, qryArvestDebits, qryArvestDeposits,
qryArvestCreditPayments;

Use the Nz function like this ---
Balance:Nz([SumOfAmount4],0)-Nz([SumOfAmount3],0)-Nz([SumOfAmount2],0)-Nz([SumOfAmount1],0)

Second, If I've made three deposits, one debit, three checks...

Post your SQL.
--
KARL DEWEY
Build a little - Test a little

I'm trying to design a database that will track my bank accounts and credit
cards. Thus far, everything works, except for my "Current Balance" queries.

I've designed the queries to sum each amount, and then set up a field for
Balance using the Expression:
Balance:[SumOfAmount4]-[SumOfAmount3]-[SumOfAmount2]-[SumOfAmount1], with
each Sum representing in order, Deposits, Checks, Debits, and CCPayments.

First, it only works if I have entered an amount in all four categories.
How do I make this work without having to enter an amount, and how do I set
the default to zero $?

Second, If I've made three deposits, one debit, three checks, and one CC
payment, the expression yields a balance that reflects three times the one
debit and three times the one CC Payment. In other words, it multiplies the
one debit via the sum function. How do I correct this?

 

[Klatuu]

You have what is called a Cartasian expression. That is, your query does not
join the queries it is using, so it will return all possible combinations.

The underlying problem is your database design. You should not have a
different table for each account, but one table with a field that identified
the account type and one that identifies the specific account number. Then
when you want to only work with credit cards, you filter on the account type
field
--
Dave Hargis, Microsoft Access MVP

Here is my SQL

SELECT qryArvestDeposits.SumOfAmount4, qryArvestCreditPayments.SumOfAmount3,
qryArvestChecks.SumOfAmount2, qryArvestDebits.SumOfAmount1,
[SumOfAmount4]-[SumOfAmount3]-[SumOfAmount2]-[SumOfAmount1] AS Balance
FROM qryArvestChecks, qryArvestDebits, qryArvestDeposits,
qryArvestCreditPayments;

Use the Nz function like this ---
Balance:Nz([SumOfAmount4],0)-Nz([SumOfAmount3],0)-Nz([SumOfAmount2],0)-Nz([SumOfAmount1],0)

Second, If I've made three deposits, one debit, three checks...

Post your SQL.
--
KARL DEWEY
Build a little - Test a little

I'm trying to design a database that will track my bank accounts and credit
cards. Thus far, everything works, except for my "Current Balance" queries.

I've designed the queries to sum each amount, and then set up a field for
Balance using the Expression:
Balance:[SumOfAmount4]-[SumOfAmount3]-[SumOfAmount2]-[SumOfAmount1], with
each Sum representing in order, Deposits, Checks, Debits, and CCPayments.

First, it only works if I have entered an amount in all four categories.
How do I make this work without having to enter an amount, and how do I set
the default to zero $?

Second, If I've made three deposits, one debit, three checks, and one CC
payment, the expression yields a balance that reflects three times the one
debit and three times the one CC Payment. In other words, it multiplies the
one debit via the sum function. How do I correct this?