Welcome to SPN

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

Sign Up Now!

Why doesn't my Form update my Master Table

Discussion in 'Information Technology' started by Debbie, Nov 5, 2005.

  1. Debbie

    Debbie
    Expand Collapse
    Guest

    I have a form that I have my sales guys update for their accounts but it
    doesn't update the Master Table I have created to capture these updates. The
    form is built off of a query which then pulls from the Master Table. When I
    run the report it isn't updating my numbers in my sum column. What could I
    be doing wrong? I thought that no matter where I updated the main table
    would reflect those updates and changes?? but it isn't.

    Thanks for anyone's help!

    Deb.
     
  2. Loading...


  3. Rick B

    Rick B
    Expand Collapse
    Guest

    Yes, it should update the table. After the user makes an entry, they are
    moving to another record or closing the form right? In other words, they
    are saving the change?

    After a change is made, if you go look at that table do you see it there?



    --
    Rick B



    "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    news:23EF82D1-7A13-4E21-BBA3-CF0D45D1F5D8@microsoft.com...
    >I have a form that I have my sales guys update for their accounts but it
    > doesn't update the Master Table I have created to capture these updates.
    > The
    > form is built off of a query which then pulls from the Master Table. When
    > I
    > run the report it isn't updating my numbers in my sum column. What could
    > I
    > be doing wrong? I thought that no matter where I updated the main table
    > would reflect those updates and changes?? but it isn't.
    >
    > Thanks for anyone's help!
    >
    > Deb.
     
  4. Debbie

    Debbie
    Expand Collapse
    Guest

    Thanks Rick - Actually I just ran a test and I think I know the problem but I
    don't know how to fix it. In my form I have the below for example:

    Percentage x Total at Odds = FY06Qtr1

    The person enters the percentage say .33 this then auto calc's into the
    FY06Qtr1 column. I see it on the form but it does not reflect the numbers on
    the Master Table. The percentages show up in the Master Table but not on the
    FY06Qtr1 column. So for some reason it is not picking up the auto calc's in
    the one column which throws my report off.

    Did that make sense?

    Thanks again.
    Deb.

    "Rick B" wrote:

    > Yes, it should update the table. After the user makes an entry, they are
    > moving to another record or closing the form right? In other words, they
    > are saving the change?
    >
    > After a change is made, if you go look at that table do you see it there?
    >
    >
    >
    > --
    > Rick B
    >
    >
    >
    > "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    > news:23EF82D1-7A13-4E21-BBA3-CF0D45D1F5D8@microsoft.com...
    > >I have a form that I have my sales guys update for their accounts but it
    > > doesn't update the Master Table I have created to capture these updates.
    > > The
    > > form is built off of a query which then pulls from the Master Table. When
    > > I
    > > run the report it isn't updating my numbers in my sum column. What could
    > > I
    > > be doing wrong? I thought that no matter where I updated the main table
    > > would reflect those updates and changes?? but it isn't.
    > >
    > > Thanks for anyone's help!
    > >
    > > Deb.

    >
    >
    >
     
  5. Rick B

    Rick B
    Expand Collapse
    Guest

    Yes, that makes sense. You don't store calculated values in a table. You
    store the various numbers that make up the calculation, then you use a
    formula in your queries, reports, or forms to create the calculated value.

    --
    Rick B



    "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    news:64388297-A0C0-4215-BBFC-DD9F29C704B1@microsoft.com...
    > Thanks Rick - Actually I just ran a test and I think I know the problem
    > but I
    > don't know how to fix it. In my form I have the below for example:
    >
    > Percentage x Total at Odds = FY06Qtr1
    >
    > The person enters the percentage say .33 this then auto calc's into the
    > FY06Qtr1 column. I see it on the form but it does not reflect the numbers
    > on
    > the Master Table. The percentages show up in the Master Table but not on
    > the
    > FY06Qtr1 column. So for some reason it is not picking up the auto calc's
    > in
    > the one column which throws my report off.
    >
    > Did that make sense?
    >
    > Thanks again.
    > Deb.
    >
    > "Rick B" wrote:
    >
    >> Yes, it should update the table. After the user makes an entry, they are
    >> moving to another record or closing the form right? In other words, they
    >> are saving the change?
    >>
    >> After a change is made, if you go look at that table do you see it there?
    >>
    >>
    >>
    >> --
    >> Rick B
    >>
    >>
    >>
    >> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    >> news:23EF82D1-7A13-4E21-BBA3-CF0D45D1F5D8@microsoft.com...
    >> >I have a form that I have my sales guys update for their accounts but it
    >> > doesn't update the Master Table I have created to capture these
    >> > updates.
    >> > The
    >> > form is built off of a query which then pulls from the Master Table.
    >> > When
    >> > I
    >> > run the report it isn't updating my numbers in my sum column. What
    >> > could
    >> > I
    >> > be doing wrong? I thought that no matter where I updated the main
    >> > table
    >> > would reflect those updates and changes?? but it isn't.
    >> >
    >> > Thanks for anyone's help!
    >> >
    >> > Deb.

    >>
    >>
    >>
     
  6. Debbie

    Debbie
    Expand Collapse
    Guest

    Rick:

    How do I create the formula in my queries? I think here lies my problem
    then. I have the reports pulling from the queries that pulls from the master
    table. If it isn't auto calculating those numbers in the querie then it will
    not show the updates in my master table.

    "Rick B" wrote:

    > Yes, that makes sense. You don't store calculated values in a table. You
    > store the various numbers that make up the calculation, then you use a
    > formula in your queries, reports, or forms to create the calculated value.
    >
    > --
    > Rick B
    >
    >
    >
    > "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    > news:64388297-A0C0-4215-BBFC-DD9F29C704B1@microsoft.com...
    > > Thanks Rick - Actually I just ran a test and I think I know the problem
    > > but I
    > > don't know how to fix it. In my form I have the below for example:
    > >
    > > Percentage x Total at Odds = FY06Qtr1
    > >
    > > The person enters the percentage say .33 this then auto calc's into the
    > > FY06Qtr1 column. I see it on the form but it does not reflect the numbers
    > > on
    > > the Master Table. The percentages show up in the Master Table but not on
    > > the
    > > FY06Qtr1 column. So for some reason it is not picking up the auto calc's
    > > in
    > > the one column which throws my report off.
    > >
    > > Did that make sense?
    > >
    > > Thanks again.
    > > Deb.
    > >
    > > "Rick B" wrote:
    > >
    > >> Yes, it should update the table. After the user makes an entry, they are
    > >> moving to another record or closing the form right? In other words, they
    > >> are saving the change?
    > >>
    > >> After a change is made, if you go look at that table do you see it there?
    > >>
    > >>
    > >>
    > >> --
    > >> Rick B
    > >>
    > >>
    > >>
    > >> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    > >> news:23EF82D1-7A13-4E21-BBA3-CF0D45D1F5D8@microsoft.com...
    > >> >I have a form that I have my sales guys update for their accounts but it
    > >> > doesn't update the Master Table I have created to capture these
    > >> > updates.
    > >> > The
    > >> > form is built off of a query which then pulls from the Master Table.
    > >> > When
    > >> > I
    > >> > run the report it isn't updating my numbers in my sum column. What
    > >> > could
    > >> > I
    > >> > be doing wrong? I thought that no matter where I updated the main
    > >> > table
    > >> > would reflect those updates and changes?? but it isn't.
    > >> >
    > >> > Thanks for anyone's help!
    > >> >
    > >> > Deb.
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Rick B

    Rick B
    Expand Collapse
    Guest

    Delete the calculated field from your table. I will assume it does not have
    any valid data in it at this time, right?

    To calculate it in your query, add a new column to your query and put the
    following "FIELD:" field...

    FY06Qtr1: [Percentage] * [Total at Odds]

    Then, you can use "FY06Qtr1" in your reports.

    I'm concerned though. This field name appears to be Fiscal Year 06 Quarter
    1. I'm worried that you might have an issue with your database design here.
    Does this table not contain data for Q2? In a normalized database design,
    you'd generally not create duplicate tables with identical designs, but
    store different date ranges in them. Instead, you'd include a date in your
    detail records and then you'd pull out the valid date ranges. But, that is
    a separate discussion.

    Post a new thread with some design details if you want help normalizing your
    design.


    --
    Rick B



    "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    news:A2982404-1801-4406-8FBD-A132A56EE745@microsoft.com...
    > Rick:
    >
    > How do I create the formula in my queries? I think here lies my problem
    > then. I have the reports pulling from the queries that pulls from the
    > master
    > table. If it isn't auto calculating those numbers in the querie then it
    > will
    > not show the updates in my master table.
    >
    > "Rick B" wrote:
    >
    >> Yes, that makes sense. You don't store calculated values in a table.
    >> You
    >> store the various numbers that make up the calculation, then you use a
    >> formula in your queries, reports, or forms to create the calculated
    >> value.
    >>
    >> --
    >> Rick B
    >>
    >>
    >>
    >> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    >> news:64388297-A0C0-4215-BBFC-DD9F29C704B1@microsoft.com...
    >> > Thanks Rick - Actually I just ran a test and I think I know the problem
    >> > but I
    >> > don't know how to fix it. In my form I have the below for example:
    >> >
    >> > Percentage x Total at Odds = FY06Qtr1
    >> >
    >> > The person enters the percentage say .33 this then auto calc's into the
    >> > FY06Qtr1 column. I see it on the form but it does not reflect the
    >> > numbers
    >> > on
    >> > the Master Table. The percentages show up in the Master Table but not
    >> > on
    >> > the
    >> > FY06Qtr1 column. So for some reason it is not picking up the auto
    >> > calc's
    >> > in
    >> > the one column which throws my report off.
    >> >
    >> > Did that make sense?
    >> >
    >> > Thanks again.
    >> > Deb.
    >> >
    >> > "Rick B" wrote:
    >> >
    >> >> Yes, it should update the table. After the user makes an entry, they
    >> >> are
    >> >> moving to another record or closing the form right? In other words,
    >> >> they
    >> >> are saving the change?
    >> >>
    >> >> After a change is made, if you go look at that table do you see it
    >> >> there?
    >> >>
    >> >>
    >> >>
    >> >> --
    >> >> Rick B
    >> >>
    >> >>
    >> >>
    >> >> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    >> >> news:23EF82D1-7A13-4E21-BBA3-CF0D45D1F5D8@microsoft.com...
    >> >> >I have a form that I have my sales guys update for their accounts but
    >> >> >it
    >> >> > doesn't update the Master Table I have created to capture these
    >> >> > updates.
    >> >> > The
    >> >> > form is built off of a query which then pulls from the Master Table.
    >> >> > When
    >> >> > I
    >> >> > run the report it isn't updating my numbers in my sum column. What
    >> >> > could
    >> >> > I
    >> >> > be doing wrong? I thought that no matter where I updated the main
    >> >> > table
    >> >> > would reflect those updates and changes?? but it isn't.
    >> >> >
    >> >> > Thanks for anyone's help!
    >> >> >
    >> >> > Deb.
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  8. Debbie

    Debbie
    Expand Collapse
    Guest

    Rick:
    Thanks for your help - I'm so frustrated right now because I feel it is
    something simple I am missing. I've added a link to give you a snap shot of
    what I did using the wizards. The form has calcualated field within it that
    I thought should upload to the main table?

    http://home.earthlink.net/~debb66/

    Thanks again.

    Debbie
    "Rick B" wrote:

    > Delete the calculated field from your table. I will assume it does not have
    > any valid data in it at this time, right?
    >
    > To calculate it in your query, add a new column to your query and put the
    > following "FIELD:" field...
    >
    > FY06Qtr1: [Percentage] * [Total at Odds]
    >
    > Then, you can use "FY06Qtr1" in your reports.
    >
    > I'm concerned though. This field name appears to be Fiscal Year 06 Quarter
    > 1. I'm worried that you might have an issue with your database design here.
    > Does this table not contain data for Q2? In a normalized database design,
    > you'd generally not create duplicate tables with identical designs, but
    > store different date ranges in them. Instead, you'd include a date in your
    > detail records and then you'd pull out the valid date ranges. But, that is
    > a separate discussion.
    >
    > Post a new thread with some design details if you want help normalizing your
    > design.
    >
    >
    > --
    > Rick B
    >
    >
    >
    > "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    > news:A2982404-1801-4406-8FBD-A132A56EE745@microsoft.com...
    > > Rick:
    > >
    > > How do I create the formula in my queries? I think here lies my problem
    > > then. I have the reports pulling from the queries that pulls from the
    > > master
    > > table. If it isn't auto calculating those numbers in the querie then it
    > > will
    > > not show the updates in my master table.
    > >
    > > "Rick B" wrote:
    > >
    > >> Yes, that makes sense. You don't store calculated values in a table.
    > >> You
    > >> store the various numbers that make up the calculation, then you use a
    > >> formula in your queries, reports, or forms to create the calculated
    > >> value.
    > >>
    > >> --
    > >> Rick B
    > >>
    > >>
    > >>
    > >> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    > >> news:64388297-A0C0-4215-BBFC-DD9F29C704B1@microsoft.com...
    > >> > Thanks Rick - Actually I just ran a test and I think I know the problem
    > >> > but I
    > >> > don't know how to fix it. In my form I have the below for example:
    > >> >
    > >> > Percentage x Total at Odds = FY06Qtr1
    > >> >
    > >> > The person enters the percentage say .33 this then auto calc's into the
    > >> > FY06Qtr1 column. I see it on the form but it does not reflect the
    > >> > numbers
    > >> > on
    > >> > the Master Table. The percentages show up in the Master Table but not
    > >> > on
    > >> > the
    > >> > FY06Qtr1 column. So for some reason it is not picking up the auto
    > >> > calc's
    > >> > in
    > >> > the one column which throws my report off.
    > >> >
    > >> > Did that make sense?
    > >> >
    > >> > Thanks again.
    > >> > Deb.
    > >> >
    > >> > "Rick B" wrote:
    > >> >
    > >> >> Yes, it should update the table. After the user makes an entry, they
    > >> >> are
    > >> >> moving to another record or closing the form right? In other words,
    > >> >> they
    > >> >> are saving the change?
    > >> >>
    > >> >> After a change is made, if you go look at that table do you see it
    > >> >> there?
    > >> >>
    > >> >>
    > >> >>
    > >> >> --
    > >> >> Rick B
    > >> >>
    > >> >>
    > >> >>
    > >> >> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
    > >> >> news:23EF82D1-7A13-4E21-BBA3-CF0D45D1F5D8@microsoft.com...
    > >> >> >I have a form that I have my sales guys update for their accounts but
    > >> >> >it
    > >> >> > doesn't update the Master Table I have created to capture these
    > >> >> > updates.
    > >> >> > The
    > >> >> > form is built off of a query which then pulls from the Master Table.
    > >> >> > When
    > >> >> > I
    > >> >> > run the report it isn't updating my numbers in my sum column. What
    > >> >> > could
    > >> >> > I
    > >> >> > be doing wrong? I thought that no matter where I updated the main
    > >> >> > table
    > >> >> > would reflect those updates and changes?? but it isn't.
    > >> >> >
    > >> >> > Thanks for anyone's help!
    > >> >> >
    > >> >> > Deb.
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sat, 5 Nov 2005 05:37:04 -0800, Debbie
    <Debbie@discussions.microsoft.com> wrote:

    >Rick:
    >Thanks for your help - I'm so frustrated right now because I feel it is
    >something simple I am missing. I've added a link to give you a snap shot of
    >what I did using the wizards. The form has calcualated field within it that
    >I thought should upload to the main table?
    >
    >http://home.earthlink.net/~debb66/


    Rick's right: your data structure IS INCORRECT. You're "committing
    spreadsheet upon a database" - each record in your table contains a
    one-to-many relationship to what should be another table. That's why
    you're having so much difficulty!

    Two basic principles:

    - Don't store data in fieldnames. If you have (as you do) fields named
    Per1, Per2, Per3 or FY06Qtr1December or the like - you're off track.
    - Don't store derived data (the point of your original question).
    Storing derived data such as this in your table accomplishes
    three things: it wastes disk space; it wastes time (almost
    any calculation will be MUCH faster than a disk fetch); and
    most importantly, it risks data corruption. If one of the
    underlying fields is subsequently edited, you will have data
    in your table WHICH IS WRONG, and no automatic way to detect
    that fact.

    Just redo the calculation whenever you need it, either as a
    calculated field in a Query or just as you're now doing it -
    in the control source of a Form or a Report textbox.

    You need to restructure your table into TWO tables; the second table
    should have fields for the Per (whatever that might be), the Quarter
    (a date/time field perhaps), and the Amount (what's now in your
    FY06Qtr1December field). "Fields are expensive, records are cheap" -
    you can CALCULATE the subtotals and totals using a Totals query or on
    a form, there's no need to store them in your table.

    John W. Vinson[MVP]
     
  10. Debbie

    Debbie
    Expand Collapse
    Guest

    Thanks John! Makes perfect logical sense!!! I'm knew at this database stuff
    and well its been a tough road and a lot of pressure for me to do this.

    Thanks again it is most appreciated!
    Deb.

    "John Vinson" wrote:

    > On Sat, 5 Nov 2005 05:37:04 -0800, Debbie
    > <Debbie@discussions.microsoft.com> wrote:
    >
    > >Rick:
    > >Thanks for your help - I'm so frustrated right now because I feel it is
    > >something simple I am missing. I've added a link to give you a snap shot of
    > >what I did using the wizards. The form has calcualated field within it that
    > >I thought should upload to the main table?
    > >
    > >http://home.earthlink.net/~debb66/

    >
    > Rick's right: your data structure IS INCORRECT. You're "committing
    > spreadsheet upon a database" - each record in your table contains a
    > one-to-many relationship to what should be another table. That's why
    > you're having so much difficulty!
    >
    > Two basic principles:
    >
    > - Don't store data in fieldnames. If you have (as you do) fields named
    > Per1, Per2, Per3 or FY06Qtr1December or the like - you're off track.
    > - Don't store derived data (the point of your original question).
    > Storing derived data such as this in your table accomplishes
    > three things: it wastes disk space; it wastes time (almost
    > any calculation will be MUCH faster than a disk fetch); and
    > most importantly, it risks data corruption. If one of the
    > underlying fields is subsequently edited, you will have data
    > in your table WHICH IS WRONG, and no automatic way to detect
    > that fact.
    >
    > Just redo the calculation whenever you need it, either as a
    > calculated field in a Query or just as you're now doing it -
    > in the control source of a Form or a Report textbox.
    >
    > You need to restructure your table into TWO tables; the second table
    > should have fields for the Per (whatever that might be), the Quarter
    > (a date/time field perhaps), and the Amount (what's now in your
    > FY06Qtr1December field). "Fields are expensive, records are cheap" -
    > you can CALCULATE the subtotals and totals using a Totals query or on
    > a form, there's no need to store them in your table.
    >
    > John W. Vinson[MVP]
    >
     
  11. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sat, 5 Nov 2005 12:04:02 -0800, Debbie
    <Debbie@discussions.microsoft.com> wrote:

    >Thanks John! Makes perfect logical sense!!! I'm knew at this database stuff
    >and well its been a tough road and a lot of pressure for me to do this.


    It *is* tough. The fact that Access is part of the Office package,
    along with Word, Excel, and PowerPoint, gives the impression that it's
    something you can just pick up and run with... and you can, but as you
    see, you can easily run into some very thorny thickets doing so!

    Don't hesitate to post back with your revised table structure for
    advice. Also check out the links at www.mvps.org/access and/or at Jeff
    Conrad's great site:

    http://home.bendbroadband.com/conradsystems/accessjunkie.html

    Start with the Resources... Starting Out link.

    John W. Vinson[MVP]
     
  12. Debbie

    Debbie
    Expand Collapse
    Guest

    John:

    I just want to thank you ONE MORE TIME! :) What a saving grace this was! I
    created another database and used the query to run my numbers and oh what a
    beautiful thing. I then created the form based off of that query along with
    the report and everything looks beautiful. The nice thing is that all I have
    to do is enter in the percentage and it auto calc's on the form as well as
    the report. I don't have to continually create the formulas in my form or
    records now. Plus when I go in and update the records it automatically
    updates the report and the numbers are correct! :)

    Thanks again!

    Deb.

    "John Vinson" wrote:

    > On Sat, 5 Nov 2005 12:04:02 -0800, Debbie
    > <Debbie@discussions.microsoft.com> wrote:
    >
    > >Thanks John! Makes perfect logical sense!!! I'm knew at this database stuff
    > >and well its been a tough road and a lot of pressure for me to do this.

    >
    > It *is* tough. The fact that Access is part of the Office package,
    > along with Word, Excel, and PowerPoint, gives the impression that it's
    > something you can just pick up and run with... and you can, but as you
    > see, you can easily run into some very thorny thickets doing so!
    >
    > Don't hesitate to post back with your revised table structure for
    > advice. Also check out the links at www.mvps.org/access and/or at Jeff
    > Conrad's great site:
    >
    > http://home.bendbroadband.com/conradsystems/accessjunkie.html
    >
    > Start with the Resources... Starting Out link.
    >
    > John W. Vinson[MVP]
    >
    >
    >
     
  13. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 6 Nov 2005 16:21:44 -0800, Debbie
    <Debbie@discussions.microsoft.com> wrote:

    >John:
    >
    >I just want to thank you ONE MORE TIME! :) What a saving grace this was! I
    >created another database and used the query to run my numbers and oh what a
    >beautiful thing. I then created the form based off of that query along with
    >the report and everything looks beautiful. The nice thing is that all I have
    >to do is enter in the percentage and it auto calc's on the form as well as
    >the report. I don't have to continually create the formulas in my form or
    >records now. Plus when I go in and update the records it automatically
    >updates the report and the numbers are correct! :)
    >
    >Thanks again!


    You're most welcome, Debbie. And thank you for letting me know - it's
    great to hear that my post helped, and it's *so* much fun to see the
    light coming on!


    John W. Vinson[MVP]
     

Share This Page