Why doesn't my Form update my Master Table

D

Debbie

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

Rick B

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?
 
D

Debbie

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

Rick B

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

Debbie

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

Rick B

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

Debbie

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

John Vinson

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/
Click to expand...

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]
 
D

Debbie

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

John Vinson

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.
Click to expand...

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]
 
D

Debbie

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

John Vinson

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!
Click to expand...

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]
 
Top