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.

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Underpaidadmin" <(E-Mail Removed)> wrote in message
news:9AD822ED-C522-474F-8EB5-(E-Mail Removed)...
>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.

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


"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Underpaidadmin" <(E-Mail Removed)> wrote in message
> news:9AD822ED-C522-474F-8EB5-(E-Mail Removed)...
> >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.
>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Underpaidadmin" <(E-Mail Removed)> wrote in message
news:232B1A49-1CE5-4C98-A05A-(E-Mail Removed)...
> [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.
>
>
> "Allen Browne" wrote:
>
>> 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.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Underpaidadmin" <(E-Mail Removed)> wrote in
>> message
>> news:9AD822ED-C522-474F-8EB5-(E-Mail Removed)...
>> >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.
>>
>>
>>

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;


"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Underpaidadmin" <(E-Mail Removed)> wrote in message
> news:232B1A49-1CE5-4C98-A05A-(E-Mail Removed)...
> > [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.
> >
> >
> > "Allen Browne" wrote:
> >
> >> 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.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Underpaidadmin" <(E-Mail Removed)> wrote in
> >> message
> >> news:9AD822ED-C522-474F-8EB5-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Underpaidadmin" <(E-Mail Removed)> wrote in message
news:AA415A3D-88E9-42D1-949D-(E-Mail Removed)...
> 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;
>
>
> "Allen Browne" wrote:
>
>> 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.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Underpaidadmin" <(E-Mail Removed)> wrote in
>> message
>> news:232B1A49-1CE5-4C98-A05A-(E-Mail Removed)...
>> > [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.
>> >
>> >
>> > "Allen Browne" wrote:
>> >
>> >> 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.
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Underpaidadmin" <(E-Mail Removed)> wrote in
>> >> message
>> >> news:9AD822ED-C522-474F-8EB5-(E-Mail Removed)...
>> >> >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.

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]);


"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Underpaidadmin" <(E-Mail Removed)> wrote in message
> news:AA415A3D-88E9-42D1-949D-(E-Mail Removed)...
> > 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;
> >
> >
> > "Allen Browne" wrote:
> >
> >> 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.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Underpaidadmin" <(E-Mail Removed)> wrote in
> >> message
> >> news:232B1A49-1CE5-4C98-A05A-(E-Mail Removed)...
> >> > [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.
> >> >
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> 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.
> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >>
> >> >> "Underpaidadmin" <(E-Mail Removed)> wrote in
> >> >> message
> >> >> news:9AD822ED-C522-474F-8EB5-(E-Mail Removed)...
> >> >> >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.
>
>
>