Guru needed-Calculations on one form?

S

SAL

I am trying to build a form that updates and places the calculated data on
one form but I am having difficulty. I have a set amount of records that will
not change, the only thing that will change is a user data entry box, and
the total column box. I have a Units box, And a RVU box (Users enter a new
number in this field) and a WTD units box (This would be the total of the
Units box * the RVU box) I then want to take the sum of all the WTD units
boxes that have the same GL# (some units will have the same GL#) and sort by
GL#. Nothing I have tried works. Here is how one line would look.
GL#| Units| RVU | WTD Units | Total WTD Units
there could be some columns with the same GL#
Example:
GL#| Units| RVU | WTD Units | Total WTD Units |
600 | 1 | 10 | 10 | 100 |
600 | 3 | 30 | 90 | 100 |
601 | 2 | 20 | 40 | 40 |

The Total WTD Units column would have to look for all GL# 600's and sum the
WTD Units column and display in the Total WTD Units column the Sum of all. I
have been asked to give this a shot for an easy way to see the data from one
form window but I think that my access skills have diminished too much to
figure it out. I am able to do it using querry's but the data needs to be on
the same form. Any help or advice would be appreciated, or if I should just
"Punt this thing away", let me know.
 
S

Steve Schapel

SAL,

I assume the GL#, Units, and RVU, are all bound to fields in your table?
If so, you could use an expression like this in the Total textbox's
Control Source...
=DSum("[Units]*[RVU]","YourTableName","[GL#]=" & [GL#])
 
S

SAL

Steve,
Very close to what I have been trying but I am still getting the #Name?
error message. I was not sure if I could do all the calculations at one time.
For some reason it seems that the equation is not liked very well by access.
 
S

Steve Schapel

SAL,

What do you mean by "very close"? What exactly is the expression you
are using, which results in the #Name?? If Access doesn't like the
expression, it is because there is something wrong with it. So, let's
see it, and we might be able to track down what's wrong.
 
S

SAL

I started out toying around with this
SELECT [DATA TABLE1].[GL #], Sum([DATA TABLE1].[WTD UNITS])
GROUP BY [DATA TABLE1].[GL #];
Then tried yours, and I had the same error return. I was wondering if I am
incapable of doing the equation on the form since I am pulling the data from
multiple tables. I thought that I could extract the data from the form page
but I figured that there must be some type of syntax error that access does
not like and shoots me out the error
 
S

Steve Schapel

SAL said:
... I am pulling the data from
multiple tables.
Click to expand...

You are? Like what?
...
I figured that there must be some type of syntax error that access does
not like and shoots me out the error
Click to expand...

Probably. If we can't see the expression, I guess we'll never know.
 
Top