HELP PLEASE! Question about Expression Building and data entry.

  • Thread starter Thread starter Viken Karaguesian
  • Start date Start date
V

Viken Karaguesian

Hi all,

I hope someone can help me.

I'm building a database for a cousing of mine. In it, she wants payments to
be totalled. For that I have four fields: Payment01, Payment02, Payment03,
Payment04. These four fields are totalled into a 5th field called "Total
Payments". I made a form for the table.

I was able to figure out how to total the four fields: I brought up the
properties to the "Total Payments" field and in the "Control Source" box I
built an expression; =Payment01 + Payment02 + Payment03 + Payment04.

This expression worked - the four fields were totalled in the last field.
The problem is the final data does not make it into the table. When I open
the form, I can see the proper totals and values in the "Total Payments"
field, but when I open the table the "Total Payments" fields are ALL EMPTY.

I suppose that the underlying problem is that I changed the "control source"
of the "Total Payments" field. HOw can I get Access to total the four fields
AND put the results in the table? Any help would be appreciated. Thanks in
advance.

Viken Karaguesian
 
Hi,


Why would you stored them in a table, if you can produce a query that
does the computation? use the query, where you intended to use the table,
with its "total". It is probably faster (to make the computation than to
wait the hard disk to spin and pump the extra data), but more importantly,
it will be UP TO DATE (while stored data is only accurate as goes it latest
time it has been computed) at the time you open it, every time you open it.


Hoping it may help,
Vanderghast, Access MVP
 
Does not sound like you have a normalized table structure. What if you end
up with 8 payments? what if you only have 2? Your payments should be in
their own table. This is a classic one-to-many relationship.

One customer, many payments.

Also, you should not store calculated fields.

You should consider your structure and get it normalized first.


Rick B

Hi all,

I hope someone can help me.

I'm building a database for a cousing of mine. In it, she wants payments to
be totalled. For that I have four fields: Payment01, Payment02, Payment03,
Payment04. These four fields are totalled into a 5th field called "Total
Payments". I made a form for the table.

I was able to figure out how to total the four fields: I brought up the
properties to the "Total Payments" field and in the "Control Source" box I
built an expression; =Payment01 + Payment02 + Payment03 + Payment04.

This expression worked - the four fields were totalled in the last field.
The problem is the final data does not make it into the table. When I open
the form, I can see the proper totals and values in the "Total Payments"
field, but when I open the table the "Total Payments" fields are ALL EMPTY.

I suppose that the underlying problem is that I changed the "control source"
of the "Total Payments" field. HOw can I get Access to total the four fields
AND put the results in the table? Any help would be appreciated. Thanks in
advance.

Viken Karaguesian
 
Also, the reason it is not storing is because you changed the control
source. If you took out the field name, how would it know where to store
the results? If you insist on keeping this poor design, you would need to
put the total field back in yout control source. Somewhere in code (maybe
in the before update) you'd need to tell it to slam a value in that field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
TotalPayments=Payment01+Payment02+Payment03+Payment04
End Sub





Hi all,

I hope someone can help me.

I'm building a database for a cousing of mine. In it, she wants payments to
be totalled. For that I have four fields: Payment01, Payment02, Payment03,
Payment04. These four fields are totalled into a 5th field called "Total
Payments". I made a form for the table.

I was able to figure out how to total the four fields: I brought up the
properties to the "Total Payments" field and in the "Control Source" box I
built an expression; =Payment01 + Payment02 + Payment03 + Payment04.

This expression worked - the four fields were totalled in the last field.
The problem is the final data does not make it into the table. When I open
the form, I can see the proper totals and values in the "Total Payments"
field, but when I open the table the "Total Payments" fields are ALL EMPTY.

I suppose that the underlying problem is that I changed the "control source"
of the "Total Payments" field. HOw can I get Access to total the four fields
AND put the results in the table? Any help would be appreciated. Thanks in
advance.

Viken Karaguesian
 
Back
Top