Can data entered in 1 field automatically be calculated and update other fields in the table?

[F@1]

I use a form to enter data into 2 fields in a table, and would like to
know if and how it can automatically enter or update other fields in
the same table. I can do it on the form itself by using a simple
expression in the control of the other text boxes, but simply don't
know anything about writing expressions, modules or macro's to
manipulate the data in the tables itself.

Or can the data calculated on the form update the respective fields in
the table?

The format is simple, 5 fields all currency entries: labor plus parts
plus tax equals total.

so... 5 fields A through E

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

As always, Very gratefull by the help i recieve here


jeff

 

[Smartin]

I use a form to enter data into 2 fields in a table, and would like to
know if and how it can automatically enter or update other fields in
the same table. I can do it on the form itself by using a simple
expression in the control of the other text boxes, but simply don't
know anything about writing expressions, modules or macro's to
manipulate the data in the tables itself.

Or can the data calculated on the form update the respective fields in
the table?

The format is simple, 5 fields all currency entries: labor plus parts
plus tax equals total.

so... 5 fields A through E

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

As always, Very gratefull by the help i recieve here


jeff

Hi jeff,

It's not usually a good idea to put calculated values in your table.
Leave the calculations on your form, or use a query to determine them
when needed.

 

[fredg]

I use a form to enter data into 2 fields in a table, and would like to
know if and how it can automatically enter or update other fields in
the same table. I can do it on the form itself by using a simple
expression in the control of the other text boxes, but simply don't
know anything about writing expressions, modules or macro's to
manipulate the data in the tables itself.

Or can the data calculated on the form update the respective fields in
the table?

The format is simple, 5 fields all currency entries: labor plus parts
plus tax equals total.

so... 5 fields A through E

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

As always, Very gratefull by the help i recieve here


jeff

You asked this same question around 1 PM today, and it was answered a
few minutes later. Here was my response, copied from that reply.

If you don't understand the reply, instead of re-posting the same
question, ask for an explanation.

*****
You're using Access, not Excel.
There is no need to store the results of the calculation.
As long as you have FieldA and FieldB stored in a table, anytime you
need the Subtotal or Total, simply calculate it.

In a query:
Total[FieldA] + [FieldB])*1.07

Do not store this value.

Directly on a form or in a report, use an unbound text control.
Set it's control source to:
=([FieldA] + [FieldB])*1.07

 

[F@1]

I use a form to enter data into 2 fields in a table, and would like to
know if and how it can automatically enter or update other fields in
the same table. I can do it on the form itself by using a simple
expression in the control of the other text boxes, but simply don't
know anything about writing expressions, modules or macro's to
manipulate the data in the tables itself.

Or can the data calculated on the form update the respective fields in
the table?

The format is simple, 5 fields all currency entries: labor plus parts
plus tax equals total.

so... 5 fields A through E

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

As always, Very gratefull by the help i recieve here


jeff

You asked this same question around 1 PM today, and it was answered a
few minutes later. Here was my response, copied from that reply.

If you don't understand the reply, instead of re-posting the same
question, ask for an explanation.

*****
You're using Access, not Excel.
There is no need to store the results of the calculation.
As long as you have FieldA and FieldB stored in a table, anytime you
need the Subtotal or Total, simply calculate it.

In a query:
Total[FieldA] + [FieldB])*1.07

Do not store this value.

Directly on a form or in a report, use an unbound text control.
Set it's control source to:
=([FieldA] + [FieldB])*1.07

Hey Fred

Sorry, When I posted that question this afternoon I got an error
stating "could not post message" and did not get a chance to check if
it posted. thats why I resent it from home. Sorry about the mix up.

I Understand what you said about you said control source and the query
And had figured that part out before. I had hoped that I would be
able to store those results automatically in their respected fields in
the table.
But you say that can't be done in Access.
I'll just enter them manualy then
Have not even begun to try anything in Exel yet.
Any way thanks again for your reply and help
and again sorry about that mix up post.

Jeff

 

[F@1]

Hi jeff,

It's not usually a good idea to put calculated values in your table.
Leave the calculations on your form, or use a query to determine them
when needed.

I understand. I use the this data from this table and from two others
to send my customers a preprinted renewal form annualy . and was
hoping to automate the entries a bit. I'll just keep entering the data
manually . got another reply from some one else who said Access can't
do that, you need to do that in Excel. Have not even ventured there
yet. Anyway thanks for your help and input. Jeff

 

[Rick Brandt]

I understand. I use the this data from this table and from two others
to send my customers a preprinted renewal form annualy . and was
hoping to automate the entries a bit. I'll just keep entering the data
manually . got another reply from some one else who said Access can't
do that, you need to do that in Excel. Have not even ventured there
yet. Anyway thanks for your help and input. Jeff

It is not that you couldn't automate saving those entries in Access. You can
and fairly easily. The point is that it is not a good idea and is completely
unnecessary.

Use a Query for your "pre-printed renewal form" instead of your table and put
the calculation in the query.

 

[F@1]

It is not that you couldn't automate saving those entries in Access. You can
and fairly easily. The point is that it is not a good idea and is completely
unnecessary.

Use a Query for your "pre-printed renewal form" instead of your table and put
the calculation in the query.

OK, thats where I get stuck. I use a query to generate the renewal
form( Sorry I used the word form, it is actualy a report) The query
only filters out the the customers that need to renew for their
repective months. Parameter asks "which month" I type in mar, apr etc.
The report is formated to use a single page per customer. I then print
the report, and it spits out 40 to 50 Individual pages, which I mail
to my customers. I need to update these reports before I generate them
such as Expiration dates, any change in equipment, or costs.
Each customer page prints out with about 50 pieces of data held
within 3 tables: customer info, coverage info and equipment info.
Thats where I hoped to speed it up by auto filling the currency
fields.
Just so you know, I do not use any of the currency info to do my
bookkeeping. It is merely reference used so the customers know what
they covered when it expires and how much they have to pay. etc. I
also use the data to generate a service call report and keep records
for services rendered for the period of that year.

Sorry did not mean to deluge you with all this.

But anyway, could you elaborate more on how I could modify that query
so it could generate the respective calculations I asked for.
Mind you, I am very green on using Access and have used wizards to
design most my forms and reports.

 

[Rick Brandt]

OK, thats where I get stuck. I use a query to generate the renewal
form( Sorry I used the word form, it is actualy a report) The query
only filters out the the customers that need to renew for their
repective months. [snip]
But anyway, could you elaborate more on how I could modify that query
so it could generate the respective calculations I asked for.
Mind you, I am very green on using Access and have used wizards to
design most my forms and reports.

Based in your earlier post...

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

You open your query and in the first empty cell in the designer grid enter...

C: [A]+

In then next empty cell you enter...

D: ([A]+) * 0.07

In the next empty cell...

E: ([A]+) + (([A]+) * 0.07)

 

[fredg]

I use a form to enter data into 2 fields in a table, and would like to
know if and how it can automatically enter or update other fields in
the same table. I can do it on the form itself by using a simple
expression in the control of the other text boxes, but simply don't
know anything about writing expressions, modules or macro's to
manipulate the data in the tables itself.

Or can the data calculated on the form update the respective fields in
the table?

The format is simple, 5 fields all currency entries: labor plus parts
plus tax equals total.

so... 5 fields A through E

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

As always, Very gratefull by the help i recieve here


jeff

You asked this same question around 1 PM today, and it was answered a
few minutes later. Here was my response, copied from that reply.

If you don't understand the reply, instead of re-posting the same
question, ask for an explanation.

*****
You're using Access, not Excel.
There is no need to store the results of the calculation.
As long as you have FieldA and FieldB stored in a table, anytime you
need the Subtotal or Total, simply calculate it.

In a query:
Total[FieldA] + [FieldB])*1.07

Do not store this value.

Directly on a form or in a report, use an unbound text control.
Set it's control source to:
=([FieldA] + [FieldB])*1.07

Hey Fred

Sorry, When I posted that question this afternoon I got an error
stating "could not post message" and did not get a chance to check if
it posted. thats why I resent it from home. Sorry about the mix up.

I Understand what you said about you said control source and the query
And had figured that part out before. I had hoped that I would be
able to store those results automatically in their respected fields in
the table.
But you say that can't be done in Access.
I'll just enter them manualy then
Have not even begun to try anything in Exel yet.
Any way thanks again for your reply and help
and again sorry about that mix up post.

Jeff

I didn't say it couldn't be saved in a table, I said it SHOULDN'T be
saved in a table. I even gave you the method to compute the total due
in a query.

Total[FieldA] + [FieldB])*1.07

If you wanted the individual breakdown of Total Purchase, Tax, and
Total Invoice, then add 3 new columns to the query grid:

TotalPurchase:[FieldA] + [FieldB]
Tax[FieldA] + [FieldB]) * 0.07
Total[FieldA] + [FieldB]) * 1.07

Use the query as the record source of your report.

 

[F@1]

On Fri, 09 Mar 2007 20:58:19 -0500, F@1 wrote:

I use a form to enter data into 2 fields in a table, and would like to
know if and how it can automatically enter or update other fields in
the same table. I can do it on the form itself by using a simple
expression in the control of the other text boxes, but simply don't
know anything about writing expressions, modules or macro's to
manipulate the data in the tables itself.

Or can the data calculated on the form update the respective fields in
the table?

The format is simple, 5 fields all currency entries: labor plus parts
plus tax equals total.

so... 5 fields A through E

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

As always, Very gratefull by the help i recieve here


jeff





You asked this same question around 1 PM today, and it was answered a
few minutes later. Here was my response, copied from that reply.

If you don't understand the reply, instead of re-posting the same
question, ask for an explanation.

*****
You're using Access, not Excel.
There is no need to store the results of the calculation.
As long as you have FieldA and FieldB stored in a table, anytime you
need the Subtotal or Total, simply calculate it.

In a query:
Total[FieldA] + [FieldB])*1.07

Do not store this value.

Directly on a form or in a report, use an unbound text control.
Set it's control source to:
=([FieldA] + [FieldB])*1.07

Hey Fred

Sorry, When I posted that question this afternoon I got an error
stating "could not post message" and did not get a chance to check if
it posted. thats why I resent it from home. Sorry about the mix up.

I Understand what you said about you said control source and the query
And had figured that part out before. I had hoped that I would be
able to store those results automatically in their respected fields in
the table.
But you say that can't be done in Access.
I'll just enter them manualy then
Have not even begun to try anything in Exel yet.
Any way thanks again for your reply and help
and again sorry about that mix up post.

Jeff

I didn't say it couldn't be saved in a table, I said it SHOULDN'T be
saved in a table. I even gave you the method to compute the total due
in a query.

Total[FieldA] + [FieldB])*1.07

If you wanted the individual breakdown of Total Purchase, Tax, and
Total Invoice, then add 3 new columns to the query grid:

TotalPurchase:[FieldA] + [FieldB]
Tax[FieldA] + [FieldB]) * 0.07
Total[FieldA] + [FieldB]) * 1.07

Use the query as the record source of your report.

Hey thanks Fred
Appreciate it, Rick gave me a similar answer, and it worked just
great.

Like I said before, I am a super newbee to access, but once I grasp
something, I tend to use it in other applications.
Learned a little more about queries today.
And love so far what I've been able to do with Access
seems that possibilties are almost endless.
Thanks so very much, and have a great day.
Jeff

P.S. You may find me in here picking some brains in the near future,
hope I won't ruffle feathers with my simple inquiries.

 

[F@1]

OK, thats where I get stuck. I use a query to generate the renewal
form( Sorry I used the word form, it is actualy a report) The query
only filters out the the customers that need to renew for their
repective months. [snip]
But anyway, could you elaborate more on how I could modify that query
so it could generate the respective calculations I asked for.
Mind you, I am very green on using Access and have used wizards to
design most my forms and reports.

Based in your earlier post...

Field A plus Field B equals Field C
Field C multiplied by .07 equals field D
Field C plus Field D equals Field E

You open your query and in the first empty cell in the designer grid enter...

C: [A]+

In then next empty cell you enter...

D: ([A]+) * 0.07

In the next empty cell...

E: ([A]+) + (([A]+) * 0.07)

Hi Rick

Thank you very much, that worked very well.
Upon reviewing what you said earlier, I now understand what you meant
Field A and field B are the important ones and as long as the
calculations appear on the printed report, is what I was seeking.
I did not understand that I did not need to keep that data in it's own
field. However, even though i am a bit thick in grasping it quickly, I
do appreciate that I learn more as I go along, and now know i little
more about modifying queries and munipulating data

Thanks very much and I hope I was not too much of a pain.

Jeff