Aggregating query

[Guest]

I thought this should be a simple matter to figure out but I have been very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those tables
are connected to the main table, [tblCasesMain], by the field [CaseNum] which
is actually a text field. They also connect to each other by [CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field, [InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance] and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are the same
and Where [PaymentDate] >= [InitDate], do [InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate errors.
My previous workaround neglected using date but used two queries, one to sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the other to
subtract those amounts from [InitBalance] using [CaseNum] as the link.

I would rather doing the caluculation on the fly if possible maybe throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control to show
the balance, and run an after update event to refresh the form. I would
appreciate all the help anyone could provide.

 

[Allen Browne]

What is the relationship between tblBalances and tblPayments? Are they
completely separate, e.g. tblBalances contains the debit amounts (invoices)
and tblPayments contains the credit amounts (receipts)? I assume from your
description that it is something like that, and especially that tblBalances
does not contain merely some kind of running balance.

If so, the simplest solution might be to merge the 2 tables into one, with
an extra field to distinguish them. A simple way to do this is to use a
Number field, with the value -1 to represent one kind of entry and 1 the
other kind. Mark the field Required, and set its Validation Rule to:
-1 Or 1
so it can never be anything else. Now in a query, you can use:
[TransactionType] * [Amount]
and you can just sum the field. In a report, you can use the Running Sum
property to show a running balance.

If you cannot do that, you may be able to still fudge it with a UNION query
like the example below to get all the values into the one resultset, so you
can still sum the value and create the Running Sum described above.

SELECT BalanceID AS ID
[BalanceDate] AS TransactionDate,
-[Amount] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[Amount] AS TransactionAmount,
"Payment" AS TransactionType
FROM tblPayments
ORDER BY TransactionDate, TransactionType, ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I thought this should be a simple matter to figure out but I have been very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those tables
are connected to the main table, [tblCasesMain], by the field [CaseNum]
which
is actually a text field. They also connect to each other by [CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field,
[InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance] and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are the
same
and Where [PaymentDate] >= [InitDate], do
[InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate errors.
My previous workaround neglected using date but used two queries, one to
sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the other
to
subtract those amounts from [InitBalance] using [CaseNum] as the link.

I would rather doing the caluculation on the fly if possible maybe
throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control to
show
the balance, and run an after update event to refresh the form. I would
appreciate all the help anyone could provide.

 

[Guest]

[tblBalances] holds the initial balance. [tblPaymentslst] holds the payments
the person/company has made or we have received from probation towards the
balance. I have already gotten too far into using two tables to redo some of
the stuff with using one table, though it would have been better. Bear with
me a bit here, I am a complete novice with SQL. I am trying to adapt your
solution, but I do not understand the syntax too well and am getting an
error. "SELECT statement includes a reserved word or argument name that is
misspelled or missing, or the punctuation is incorrect." It 'appears' right
but I can't be sure, so I will post it below:

SELECT BalanceID AS ID
[InitDate] AS TransactionDate,
[InitBal] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[PaymentAmount] AS TransactionAmount,
"Payment" AS TransactionType,
FROM tblPaymentslst
ORDER BY TransactionDate, TransactionType, ID

Not sure how the quotes work in SQL I don't have any fields with the names
"Balance" or "Payment" so are those just new column names for the query? I
never heard of a Union before now so I am lost.

What is the relationship between tblBalances and tblPayments? Are they
completely separate, e.g. tblBalances contains the debit amounts (invoices)
and tblPayments contains the credit amounts (receipts)? I assume from your
description that it is something like that, and especially that tblBalances
does not contain merely some kind of running balance.

If so, the simplest solution might be to merge the 2 tables into one, with
an extra field to distinguish them. A simple way to do this is to use a
Number field, with the value -1 to represent one kind of entry and 1 the
other kind. Mark the field Required, and set its Validation Rule to:
-1 Or 1
so it can never be anything else. Now in a query, you can use:
[TransactionType] * [Amount]
and you can just sum the field. In a report, you can use the Running Sum
property to show a running balance.

If you cannot do that, you may be able to still fudge it with a UNION query
like the example below to get all the values into the one resultset, so you
can still sum the value and create the Running Sum described above.

SELECT BalanceID AS ID
[BalanceDate] AS TransactionDate,
-[Amount] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[Amount] AS TransactionAmount,
"Payment" AS TransactionType
FROM tblPayments
ORDER BY TransactionDate, TransactionType, ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I thought this should be a simple matter to figure out but I have been very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those tables
are connected to the main table, [tblCasesMain], by the field [CaseNum]
which
is actually a text field. They also connect to each other by [CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field,
[InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance] and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are the
same
and Where [PaymentDate] >= [InitDate], do
[InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate errors.
My previous workaround neglected using date but used two queries, one to
sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the other
to
subtract those amounts from [InitBalance] using [CaseNum] as the link.

I would rather doing the caluculation on the fly if possible maybe
throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control to
show
the balance, and run an after update event to refresh the form. I would
appreciate all the help anyone could provide.

 

[Allen Browne]

Okay, I don't think I understood your tables correctly.

tblBalance contains the opening balance for a period, based on the InitDate.

tblPayments contains all the payments.

For each period (as defined by the starting date in tblBalance.InitDate),
you want to sum the payments in that period? If so, you could use a subquery
to get the starting date of the next period from another copy of the same
table (aliased as Dupe in the example below), using that in another subquery
that gives the sum of payments between the 2 dates.

Untested aircode just to give you the idea:

SELECT BalanceID, InitDate, InitBal,
(SELECT Sum(tblPayments.PaymentAmount) AS SumOfPayments
FROM tblPayments
WHERE tblPayments.PaymentDate >= tblBalances.InitDate
AND tblPayments.PaymentDate <
Nz((SELECT Min(Dupe.InitDate) FROM tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#))
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[tblBalances] holds the initial balance. [tblPaymentslst] holds the
payments
the person/company has made or we have received from probation towards the
balance. I have already gotten too far into using two tables to redo some
of
the stuff with using one table, though it would have been better. Bear
with
me a bit here, I am a complete novice with SQL. I am trying to adapt your
solution, but I do not understand the syntax too well and am getting an
error. "SELECT statement includes a reserved word or argument name that
is
misspelled or missing, or the punctuation is incorrect." It 'appears'
right
but I can't be sure, so I will post it below:

SELECT BalanceID AS ID
[InitDate] AS TransactionDate,
[InitBal] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[PaymentAmount] AS TransactionAmount,
"Payment" AS TransactionType,
FROM tblPaymentslst
ORDER BY TransactionDate, TransactionType, ID

Not sure how the quotes work in SQL I don't have any fields with the names
"Balance" or "Payment" so are those just new column names for the query?
I
never heard of a Union before now so I am lost.

What is the relationship between tblBalances and tblPayments? Are they
completely separate, e.g. tblBalances contains the debit amounts
(invoices)
and tblPayments contains the credit amounts (receipts)? I assume from
your
description that it is something like that, and especially that
tblBalances
does not contain merely some kind of running balance.

If so, the simplest solution might be to merge the 2 tables into one,
with
an extra field to distinguish them. A simple way to do this is to use a
Number field, with the value -1 to represent one kind of entry and 1 the
other kind. Mark the field Required, and set its Validation Rule to:
-1 Or 1
so it can never be anything else. Now in a query, you can use:
[TransactionType] * [Amount]
and you can just sum the field. In a report, you can use the Running Sum
property to show a running balance.

If you cannot do that, you may be able to still fudge it with a UNION
query
like the example below to get all the values into the one resultset, so
you
can still sum the value and create the Running Sum described above.

SELECT BalanceID AS ID
[BalanceDate] AS TransactionDate,
-[Amount] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[Amount] AS TransactionAmount,
"Payment" AS TransactionType
FROM tblPayments
ORDER BY TransactionDate, TransactionType, ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

I thought this should be a simple matter to figure out but I have been
very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those
tables
are connected to the main table, [tblCasesMain], by the field [CaseNum]
which
is actually a text field. They also connect to each other by
[CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field,
[InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance] and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are the
same
and Where [PaymentDate] >= [InitDate], do
[InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate errors.
My previous workaround neglected using date but used two queries, one
to
sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the
other
to
subtract those amounts from [InitBalance] using [CaseNum] as the link.

I would rather doing the caluculation on the fly if possible maybe
throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control to
show
the balance, and run an after update event to refresh the form. I
would
appreciate all the help anyone could provide.

 

[Guest]

Well, there is one more thing, after I get that sum I want to subtract it
from the initial balance

tblBalances.InitBal. Actually I have never heard of subqueries till you
just mentioned it. I fixed up the query a little bit so it seems to be
giving me the proper sums, but I still can't get the form to do the
calculation: InitBal-SumOfPayments=CurrentBalance. Gives me a #Name error
when I try to use the expression builder to do it. I didn't think this would
be nearly this difficult for me to understand but I am having trouble so I
apologize. I don't understand how the Dupe.InitDate works, is that a syntax
shortcut to create a temporary duplicate table, and if so why couldn't I have
used the orginal table to check against the ending value?

Here is my current SQL:

SELECT (SELECT Sum(tblPaymentslst.PaymentAmount) AS SumOfPayments
FROM tblPaymentslst
WHERE tblPaymentslst.PaymentDate >= tblBalances.InitDate
AND tblBalances.CaseNum=tblPaymentslst.CaseNum
AND tblPaymentslst.PaymentDate < Nz((SELECT Min(Dupe.InitDate) FROM
tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#)) AS Expr1
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

Okay, I don't think I understood your tables correctly.

tblBalance contains the opening balance for a period, based on the InitDate.

tblPayments contains all the payments.

For each period (as defined by the starting date in tblBalance.InitDate),
you want to sum the payments in that period? If so, you could use a subquery
to get the starting date of the next period from another copy of the same
table (aliased as Dupe in the example below), using that in another subquery
that gives the sum of payments between the 2 dates.

Untested aircode just to give you the idea:

SELECT BalanceID, InitDate, InitBal,
(SELECT Sum(tblPayments.PaymentAmount) AS SumOfPayments
FROM tblPayments
WHERE tblPayments.PaymentDate >= tblBalances.InitDate
AND tblPayments.PaymentDate <
Nz((SELECT Min(Dupe.InitDate) FROM tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#))
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[tblBalances] holds the initial balance. [tblPaymentslst] holds the
payments
the person/company has made or we have received from probation towards the
balance. I have already gotten too far into using two tables to redo some
of
the stuff with using one table, though it would have been better. Bear
with
me a bit here, I am a complete novice with SQL. I am trying to adapt your
solution, but I do not understand the syntax too well and am getting an
error. "SELECT statement includes a reserved word or argument name that
is
misspelled or missing, or the punctuation is incorrect." It 'appears'
right
but I can't be sure, so I will post it below:

SELECT BalanceID AS ID
[InitDate] AS TransactionDate,
[InitBal] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[PaymentAmount] AS TransactionAmount,
"Payment" AS TransactionType,
FROM tblPaymentslst
ORDER BY TransactionDate, TransactionType, ID

Not sure how the quotes work in SQL I don't have any fields with the names
"Balance" or "Payment" so are those just new column names for the query?
I
never heard of a Union before now so I am lost.

What is the relationship between tblBalances and tblPayments? Are they
completely separate, e.g. tblBalances contains the debit amounts
(invoices)
and tblPayments contains the credit amounts (receipts)? I assume from
your
description that it is something like that, and especially that
tblBalances
does not contain merely some kind of running balance.

If so, the simplest solution might be to merge the 2 tables into one,
with
an extra field to distinguish them. A simple way to do this is to use a
Number field, with the value -1 to represent one kind of entry and 1 the
other kind. Mark the field Required, and set its Validation Rule to:
-1 Or 1
so it can never be anything else. Now in a query, you can use:
[TransactionType] * [Amount]
and you can just sum the field. In a report, you can use the Running Sum
property to show a running balance.

If you cannot do that, you may be able to still fudge it with a UNION
query
like the example below to get all the values into the one resultset, so
you
can still sum the value and create the Running Sum described above.

SELECT BalanceID AS ID
[BalanceDate] AS TransactionDate,
-[Amount] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[Amount] AS TransactionAmount,
"Payment" AS TransactionType
FROM tblPayments
ORDER BY TransactionDate, TransactionType, ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I thought this should be a simple matter to figure out but I have been
very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those
tables
are connected to the main table, [tblCasesMain], by the field [CaseNum]
which
is actually a text field. They also connect to each other by
[CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field,
[InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance] and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are the
same
and Where [PaymentDate] >= [InitDate], do
[InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate errors.
My previous workaround neglected using date but used two queries, one
to
sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the
other
to
subtract those amounts from [InitBalance] using [CaseNum] as the link.

I would rather doing the caluculation on the fly if possible maybe
throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control to
show
the balance, and run an after update event to refresh the form. I
would
appreciate all the help anyone could provide.

 

[Allen Browne]

Your main query uses tblBalances, so you should be able to add any fields
you wish from that table to the query grid.

You may then be able to use the alias from the subquery result in an
expression to give the current balance. Something like this:

SELECT BalanceID, CaseNum, InitDate, InitBalance,
(SELECT Sum(tblPaymentslst.PaymentAmount) AS SumOfPayments
FROM tblPaymentslst
WHERE tblPaymentslst.PaymentDate >= tblBalances.InitDate
AND tblBalances.CaseNum=tblPaymentslst.CaseNum
AND tblPaymentslst.PaymentDate < Nz((SELECT Min(Dupe.InitDate)
FROM tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#))
AS Payments,
tblBalances.InitBalance - [Payments] AS CurrentBalance
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, there is one more thing, after I get that sum I want to subtract it
from the initial balance

tblBalances.InitBal. Actually I have never heard of subqueries till you
just mentioned it. I fixed up the query a little bit so it seems to be
giving me the proper sums, but I still can't get the form to do the
calculation: InitBal-SumOfPayments=CurrentBalance. Gives me a #Name error
when I try to use the expression builder to do it. I didn't think this
would
be nearly this difficult for me to understand but I am having trouble so I
apologize. I don't understand how the Dupe.InitDate works, is that a
syntax
shortcut to create a temporary duplicate table, and if so why couldn't I
have
used the orginal table to check against the ending value?

Here is my current SQL:

SELECT (SELECT Sum(tblPaymentslst.PaymentAmount) AS SumOfPayments
FROM tblPaymentslst
WHERE tblPaymentslst.PaymentDate >= tblBalances.InitDate
AND tblBalances.CaseNum=tblPaymentslst.CaseNum
AND tblPaymentslst.PaymentDate < Nz((SELECT Min(Dupe.InitDate) FROM
tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#)) AS Expr1
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

Okay, I don't think I understood your tables correctly.

tblBalance contains the opening balance for a period, based on the
InitDate.

tblPayments contains all the payments.

For each period (as defined by the starting date in tblBalance.InitDate),
you want to sum the payments in that period? If so, you could use a
subquery
to get the starting date of the next period from another copy of the same
table (aliased as Dupe in the example below), using that in another
subquery
that gives the sum of payments between the 2 dates.

Untested aircode just to give you the idea:

SELECT BalanceID, InitDate, InitBal,
(SELECT Sum(tblPayments.PaymentAmount) AS SumOfPayments
FROM tblPayments
WHERE tblPayments.PaymentDate >= tblBalances.InitDate
AND tblPayments.PaymentDate <
Nz((SELECT Min(Dupe.InitDate) FROM tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#))
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

[tblBalances] holds the initial balance. [tblPaymentslst] holds the
payments
the person/company has made or we have received from probation towards
the
balance. I have already gotten too far into using two tables to redo
some
of
the stuff with using one table, though it would have been better. Bear
with
me a bit here, I am a complete novice with SQL. I am trying to adapt
your
solution, but I do not understand the syntax too well and am getting an
error. "SELECT statement includes a reserved word or argument name
that
is
misspelled or missing, or the punctuation is incorrect." It 'appears'
right
but I can't be sure, so I will post it below:

SELECT BalanceID AS ID
[InitDate] AS TransactionDate,
[InitBal] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[PaymentAmount] AS TransactionAmount,
"Payment" AS TransactionType,
FROM tblPaymentslst
ORDER BY TransactionDate, TransactionType, ID

Not sure how the quotes work in SQL I don't have any fields with the
names
"Balance" or "Payment" so are those just new column names for the
query?
I
never heard of a Union before now so I am lost.


:

What is the relationship between tblBalances and tblPayments? Are they
completely separate, e.g. tblBalances contains the debit amounts
(invoices)
and tblPayments contains the credit amounts (receipts)? I assume from
your
description that it is something like that, and especially that
tblBalances
does not contain merely some kind of running balance.

If so, the simplest solution might be to merge the 2 tables into one,
with
an extra field to distinguish them. A simple way to do this is to use
a
Number field, with the value -1 to represent one kind of entry and 1
the
other kind. Mark the field Required, and set its Validation Rule to:
-1 Or 1
so it can never be anything else. Now in a query, you can use:
[TransactionType] * [Amount]
and you can just sum the field. In a report, you can use the Running
Sum
property to show a running balance.

If you cannot do that, you may be able to still fudge it with a UNION
query
like the example below to get all the values into the one resultset,
so
you
can still sum the value and create the Running Sum described above.

SELECT BalanceID AS ID
[BalanceDate] AS TransactionDate,
-[Amount] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[Amount] AS TransactionAmount,
"Payment" AS TransactionType
FROM tblPayments
ORDER BY TransactionDate, TransactionType, ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I thought this should be a simple matter to figure out but I have
been
very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those
tables
are connected to the main table, [tblCasesMain], by the field
[CaseNum]
which
is actually a text field. They also connect to each other by
[CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field,
[InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance]
and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are
the
same
and Where [PaymentDate] >= [InitDate], do
[InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate
errors.
My previous workaround neglected using date but used two queries,
one
to
sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the
other
to
subtract those amounts from [InitBalance] using [CaseNum] as the
link.

I would rather doing the caluculation on the fly if possible maybe
throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control
to
show
the balance, and run an after update event to refresh the form. I
would
appreciate all the help anyone could provide.

 

[Guest]

I eventually got something that works, albeit not as well as I had hoped, and
thought it would be appropriate to post it here for all to learn from. Thank
you for helping me get this far. I ended up using two queries to do it, one
to sum the values of one table and the second to take that sum and subtract
it from appropriate values in another table.

Here is the query which sums the values of one table based on CaseNum:
SELECT Sum(tblPaymentslst.PaymentAmount) AS [Total Paid],
tblPaymentslst.CaseNum
FROM tblPaymentslst
GROUP BY tblPaymentslst.CaseNum;

Here is the second query which takes the first query's result and subtracts
it from the other table:
SELECT tblBalances.CaseNum, IIf(IsNull(qryPaymentAmountSum![Total
Paid]),tblBalances!InitBal,tblBalances!InitBal-qryPaymentAmountSum![Total
Paid]) AS CurBalqry
FROM qryPaymentAmountSum RIGHT JOIN tblBalances ON
qryPaymentAmountSum.CaseNum = tblBalances.CaseNum
GROUP BY tblBalances.CaseNum, IIf(IsNull(qryPaymentAmountSum![Total
Paid]),tblBalances!InitBal,tblBalances!InitBal-qryPaymentAmountSum![Total
Paid]);

Your main query uses tblBalances, so you should be able to add any fields
you wish from that table to the query grid.

You may then be able to use the alias from the subquery result in an
expression to give the current balance. Something like this:

SELECT BalanceID, CaseNum, InitDate, InitBalance,
(SELECT Sum(tblPaymentslst.PaymentAmount) AS SumOfPayments
FROM tblPaymentslst
WHERE tblPaymentslst.PaymentDate >= tblBalances.InitDate
AND tblBalances.CaseNum=tblPaymentslst.CaseNum
AND tblPaymentslst.PaymentDate < Nz((SELECT Min(Dupe.InitDate)
FROM tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#))
AS Payments,
tblBalances.InitBalance - [Payments] AS CurrentBalance
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, there is one more thing, after I get that sum I want to subtract it
from the initial balance

tblBalances.InitBal. Actually I have never heard of subqueries till you
just mentioned it. I fixed up the query a little bit so it seems to be
giving me the proper sums, but I still can't get the form to do the
calculation: InitBal-SumOfPayments=CurrentBalance. Gives me a #Name error
when I try to use the expression builder to do it. I didn't think this
would
be nearly this difficult for me to understand but I am having trouble so I
apologize. I don't understand how the Dupe.InitDate works, is that a
syntax
shortcut to create a temporary duplicate table, and if so why couldn't I
have
used the orginal table to check against the ending value?

Here is my current SQL:

SELECT (SELECT Sum(tblPaymentslst.PaymentAmount) AS SumOfPayments
FROM tblPaymentslst
WHERE tblPaymentslst.PaymentDate >= tblBalances.InitDate
AND tblBalances.CaseNum=tblPaymentslst.CaseNum
AND tblPaymentslst.PaymentDate < Nz((SELECT Min(Dupe.InitDate) FROM
tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#)) AS Expr1
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

Okay, I don't think I understood your tables correctly.

tblBalance contains the opening balance for a period, based on the
InitDate.

tblPayments contains all the payments.

For each period (as defined by the starting date in tblBalance.InitDate),
you want to sum the payments in that period? If so, you could use a
subquery
to get the starting date of the next period from another copy of the same
table (aliased as Dupe in the example below), using that in another
subquery
that gives the sum of payments between the 2 dates.

Untested aircode just to give you the idea:

SELECT BalanceID, InitDate, InitBal,
(SELECT Sum(tblPayments.PaymentAmount) AS SumOfPayments
FROM tblPayments
WHERE tblPayments.PaymentDate >= tblBalances.InitDate
AND tblPayments.PaymentDate <
Nz((SELECT Min(Dupe.InitDate) FROM tblBalances AS Dupe
WHERE Dupe.InitDate > tblBalances.InitDate),#1/1/2199#))
FROM tblBalances
ORDER BY tblBalances.InitDate, tblBalances.BalanceID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
[tblBalances] holds the initial balance. [tblPaymentslst] holds the
payments
the person/company has made or we have received from probation towards
the
balance. I have already gotten too far into using two tables to redo
some
of
the stuff with using one table, though it would have been better. Bear
with
me a bit here, I am a complete novice with SQL. I am trying to adapt
your
solution, but I do not understand the syntax too well and am getting an
error. "SELECT statement includes a reserved word or argument name
that
is
misspelled or missing, or the punctuation is incorrect." It 'appears'
right
but I can't be sure, so I will post it below:

SELECT BalanceID AS ID
[InitDate] AS TransactionDate,
[InitBal] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[PaymentAmount] AS TransactionAmount,
"Payment" AS TransactionType,
FROM tblPaymentslst
ORDER BY TransactionDate, TransactionType, ID

Not sure how the quotes work in SQL I don't have any fields with the
names
"Balance" or "Payment" so are those just new column names for the
query?
I
never heard of a Union before now so I am lost.


:

What is the relationship between tblBalances and tblPayments? Are they
completely separate, e.g. tblBalances contains the debit amounts
(invoices)
and tblPayments contains the credit amounts (receipts)? I assume from
your
description that it is something like that, and especially that
tblBalances
does not contain merely some kind of running balance.

If so, the simplest solution might be to merge the 2 tables into one,
with
an extra field to distinguish them. A simple way to do this is to use
a
Number field, with the value -1 to represent one kind of entry and 1
the
other kind. Mark the field Required, and set its Validation Rule to:
-1 Or 1
so it can never be anything else. Now in a query, you can use:
[TransactionType] * [Amount]
and you can just sum the field. In a report, you can use the Running
Sum
property to show a running balance.

If you cannot do that, you may be able to still fudge it with a UNION
query
like the example below to get all the values into the one resultset,
so
you
can still sum the value and create the Running Sum described above.

SELECT BalanceID AS ID
[BalanceDate] AS TransactionDate,
-[Amount] AS TransactionAmount,
"Balance" AS TransactionType,
FROM tblBalances
UNION ALL
SELECT PaymentID AS ID
[PaymentDate] AS TransactionDate,
[Amount] AS TransactionAmount,
"Payment" AS TransactionType
FROM tblPayments
ORDER BY TransactionDate, TransactionType, ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I thought this should be a simple matter to figure out but I have
been
very
unsuccessful repeatedly, so I am asking for help.

I have two tables, [tblBalances] and [tblPayments]. Both of those
tables
are connected to the main table, [tblCasesMain], by the field
[CaseNum]
which
is actually a text field. They also connect to each other by
[CaseNum].
Both tables, [tblBalances] and [tblPayments], have a date field,
[InitDate]
and [PaymentDate] respectively and a currency field, [InitBalance]
and
[PaymentAmount] repsectively.
What I want to do is along the logic of: Where their [CaseNum] are
the
same
and Where [PaymentDate] >= [InitDate], do
[InitBalance]-Sum([PaymentAmount]).
I tried to do this using a query but got all kinds of aggregate
errors.
My previous workaround neglected using date but used two queries,
one
to
sum
all the [PaymentAmount] fields in [tblPayments] by [CaseNum] and the
other
to
subtract those amounts from [InitBalance] using [CaseNum] as the
link.

I would rather doing the caluculation on the fly if possible maybe
throwing
something like,"=[InitBalance]-(Sum([PaymentAmount]))", in a control
to
show
the balance, and run an after update event to refresh the form. I
would
appreciate all the help anyone could provide.