Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Getting balance based on two tables

Discussion in 'Information Technology' started by Dedren, Nov 11, 2005.

  1. Dedren

    Dedren
    Expand Collapse
    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.
     
  2. Loading...


  3. tina

    tina
    Expand Collapse
    Guest

    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


    "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    news:A6D5A8B4-9A22-4134-8807-DA786C408F66@microsoft.com...
    > 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.
     
  4. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    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

    Dedren" <Dedren@discussions.microsoft.com> wrote in message
    news:A6D5A8B4-9A22-4134-8807-DA786C408F66@microsoft.com...
    >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.
     
  5. Dedren

    Dedren
    Expand Collapse
    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.

    "Dedren" wrote:

    > 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.
     
  6. tina

    tina
    Expand Collapse
    Guest

    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


    "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    news:7301A3FC-338F-489B-99E1-D7F1805103FE@microsoft.com...
    > 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.
    >
    > "Dedren" wrote:
    >
    > > 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.
     
  7. Dedren

    Dedren
    Expand Collapse
    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.

    "tina" wrote:

    > 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
    >
    >
    > "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    > news:7301A3FC-338F-489B-99E1-D7F1805103FE@microsoft.com...
    > > 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.
    > >
    > > "Dedren" wrote:
    > >
    > > > 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.

    >
    >
    >
     
  8. tina

    tina
    Expand Collapse
    Guest

    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.


    "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    news:0D137105-3692-46F8-B12C-978248ECBC52@microsoft.com...
    > 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.
    >
    > "tina" wrote:
    >
    > > 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
    > >
    > >
    > > "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    > > news:7301A3FC-338F-489B-99E1-D7F1805103FE@microsoft.com...
    > > > 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.
    > > >
    > > > "Dedren" wrote:
    > > >
    > > > > 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.

    > >
    > >
    > >
     

Share This Page