Getting balance based on two tables

[Guest]

I have been reading through some of the solutions to other of these types of
questions on balances but I can't seem to put them together to do what I
need. So here goes.

I have two tables, CASES and PAID. CASES holds hordes of information on a
case including the original exposure amount in a field called, Exposure.
CASES table also uses unique case identifier field called, Case#, to
distinguish between cases. PAID holds payments made and the date that
payment was made. PAID also uses a field called, CaseNumber, which uses the,
Case#, field from the CASES table to tell which payment is for which case.
I would like to sum the payments for each CaseNumber in the PAID table and
subtract it from the Exposure in the CASES table and put it in a field in the
CASES table called, Balance.

I am using an update query to do this currently and have the following in
the 'Update to' field:

DSum("[PaymentAmount] ","[PAID]","[CaseNumber] =[CASE]![Case#]")

When I run it I get the update warning box but it never seems to do anything
to my CASES table.

 

[tina]

i'd really recommend that you DON'T do it at all. at any given point in
time, the Balance for a given record may be incorrect - depending on when
the last payment was recorded in table PAID vs when the Update query was
last run. it would be much better to calculate the unpaid balance at runtime
for display in a form or report - that way the value is *always* current and
accurate according to the data stored in the database (obviously, when a
payment is received, the database data is not "accurate" until that payment
is posted to the PAID table, regardless of any other considerations).

having said that, the DSum() function might work with the following change,
and including the complete calculation of unpaid balance, as

[CASES].[Exposure] - DSum("[PaymentAmount]","[PAID]","[CaseNumber] = " &
[CASES].[Case#])

but i really hope you give serious consideration to the previous comments.

hth

 

[Jon Lewis]

Try:

DSum("[PaymentAmount] ","[PAID]","[CaseNumber] = " & [CASE]![Case#] )

If you enclose a variable in ""'s in an expression such as this, it will be
interpreted as a literal string.

Actually, you shouldn't be storing this type of calculated value in a table.
Just calculate it and display it when you need to.

HTH

 

[Guest]

I tried both of the solutions below and got an update error because of some
type conversion failure. I got this error with my own solutions and the help
file and search engines aren't doing much to explain what this error is about
and more importantly how to fix it.

This is a very temporary solution to our problem that has to be ready for a
board meeting on 11/22. It just needs to have all the data possible into one
table so I can make a report out of it easily. I will make a little button
or something so that I or the VP can update the query as we enter new stuff.
He only needs the report monthly and I have so little experience with Access
I had to make something that I could work with. The fact that I am here
means I have already gone beyond my means and am trying to mend a bad
situation. Given time I would properly read up and normalize the database
and establish good practice techniques.

Sorry that got so long and thank you for the help.

 

[tina]

is the field [Case#] a Text data type, rather than a Number data type? if
so, try

[CASES].[Exposure] - DSum("[PaymentAmount]","[PAID]","[CaseNumber] = '" &
[CASES].[Case#] & "'")

all of the above on one line, of course.

hth

I tried both of the solutions below and got an update error because of some
type conversion failure. I got this error with my own solutions and the help
file and search engines aren't doing much to explain what this error is about
and more importantly how to fix it.

This is a very temporary solution to our problem that has to be ready for a
board meeting on 11/22. It just needs to have all the data possible into one
table so I can make a report out of it easily. I will make a little button
or something so that I or the VP can update the query as we enter new stuff.
He only needs the report monthly and I have so little experience with Access
I had to make something that I could work with. The fact that I am here
means I have already gone beyond my means and am trying to mend a bad
situation. Given time I would properly read up and normalize the database
and establish good practice techniques.

Sorry that got so long and thank you for the help.

I have been reading through some of the solutions to other of these types of
questions on balances but I can't seem to put them together to do what I
need. So here goes.

I have two tables, CASES and PAID. CASES holds hordes of information on a
case including the original exposure amount in a field called, Exposure.
CASES table also uses unique case identifier field called, Case#, to
distinguish between cases. PAID holds payments made and the date that
payment was made. PAID also uses a field called, CaseNumber, which uses the,
Case#, field from the CASES table to tell which payment is for which case.
I would like to sum the payments for each CaseNumber in the PAID table and
subtract it from the Exposure in the CASES table and put it in a field in the
CASES table called, Balance.

I am using an update query to do this currently and have the following in
the 'Update to' field:

DSum("[PaymentAmount] ","[PAID]","[CaseNumber] =[CASE]![Case#]")

When I run it I get the update warning box but it never seems to do anything
to my CASES table.

 

[Guest]

Both fields [Case#] and [CaseNumber] are of the form: FRD05-123
Now that you mention it, there is an Input Mask on the field, would that be
the problem? This is the Input Mask: >&&&&&CCCCC;0;#
I also tried your updated code but it brings up a dialoge box and I am
unsure why.

is the field [Case#] a Text data type, rather than a Number data type? if
so, try

[CASES].[Exposure] - DSum("[PaymentAmount]","[PAID]","[CaseNumber] = '" &
[CASES].[Case#] & "'")

all of the above on one line, of course.

hth

I tried both of the solutions below and got an update error because of some
type conversion failure. I got this error with my own solutions and the help
file and search engines aren't doing much to explain what this error is about
and more importantly how to fix it.

This is a very temporary solution to our problem that has to be ready for a
board meeting on 11/22. It just needs to have all the data possible into one
table so I can make a report out of it easily. I will make a little button
or something so that I or the VP can update the query as we enter new stuff.
He only needs the report monthly and I have so little experience with Access
I had to make something that I could work with. The fact that I am here
means I have already gone beyond my means and am trying to mend a bad
situation. Given time I would properly read up and normalize the database
and establish good practice techniques.

Sorry that got so long and thank you for the help.

I have been reading through some of the solutions to other of these types of
questions on balances but I can't seem to put them together to do what I
need. So here goes.

I have two tables, CASES and PAID. CASES holds hordes of information on a
case including the original exposure amount in a field called, Exposure.
CASES table also uses unique case identifier field called, Case#, to
distinguish between cases. PAID holds payments made and the date that
payment was made. PAID also uses a field called, CaseNumber, which uses the,
Case#, field from the CASES table to tell which payment is for which case.
I would like to sum the payments for each CaseNumber in the PAID table and
subtract it from the Exposure in the CASES table and put it in a field in the
CASES table called, Balance.

I am using an update query to do this currently and have the following in
the 'Update to' field:

DSum("[PaymentAmount] ","[PAID]","[CaseNumber] =[CASE]![Case#]")

When I run it I get the update warning box but it never seems to do anything
to my CASES table.

 

[tina]

since the [Case#] includes alpha and numeric characters, presumably it's
text. to find out for sure, open the CASES table in design view and look at
the DataType assigned to the field. do the same with the PAID table and the
[CaseNumber] field.

please post your SQL statement.

Both fields [Case#] and [CaseNumber] are of the form: FRD05-123
Now that you mention it, there is an Input Mask on the field, would that be
the problem? This is the Input Mask: >&&&&&CCCCC;0;#
I also tried your updated code but it brings up a dialoge box and I am
unsure why.

is the field [Case#] a Text data type, rather than a Number data type? if
so, try

[CASES].[Exposure] - DSum("[PaymentAmount]","[PAID]","[CaseNumber] = '" &
[CASES].[Case#] & "'")

all of the above on one line, of course.

hth

I tried both of the solutions below and got an update error because of some
type conversion failure. I got this error with my own solutions and

the
help

file and search engines aren't doing much to explain what this error

is
about

and more importantly how to fix it.

This is a very temporary solution to our problem that has to be ready

for
a

board meeting on 11/22. It just needs to have all the data possible

into
one

table so I can make a report out of it easily. I will make a little button
or something so that I or the VP can update the query as we enter new stuff.
He only needs the report monthly and I have so little experience with Access
I had to make something that I could work with. The fact that I am here
means I have already gone beyond my means and am trying to mend a bad
situation. Given time I would properly read up and normalize the database
and establish good practice techniques.

Sorry that got so long and thank you for the help.

:

I have been reading through some of the solutions to other of these types of
questions on balances but I can't seem to put them together to do what I
need. So here goes.

I have two tables, CASES and PAID. CASES holds hordes of

information on
a

case including the original exposure amount in a field called, Exposure.
CASES table also uses unique case identifier field called, Case#, to
distinguish between cases. PAID holds payments made and the date that
payment was made. PAID also uses a field called, CaseNumber, which

uses
the,

Case#, field from the CASES table to tell which payment is for which case.
I would like to sum the payments for each CaseNumber in the PAID

table
and

subtract it from the Exposure in the CASES table and put it in a

field
in the

CASES table called, Balance.

I am using an update query to do this currently and have the

following
in

the 'Update to' field:

DSum("[PaymentAmount] ","[PAID]","[CaseNumber] =[CASE]![Case#]")

When I run it I get the update warning box but it never seems to do anything
to my CASES table.