Perform Mass Aggregate Function Calculation in Table

[wrldruler]

Hello,

First, I know it's against "Access Law" to save calculations in a
table, but....I want/need to.

I currently have sub-totals being calculated inside a form, using
DMax, DCount, and DSum. But these aggregate functions are slowing down
the interface and annoying me. So I have decided not to show sub-
totals on the form.

But I still need the sub-total calculations performed so the sub-total
data can be used on other forms, reports, exports, etc.

I was thinking about doing a mass-update via query when the user
finishes an entry. So rather than having a pause everytime he enters a
number, I would just wait until the end, and mass-update.

But as you know, you can't create an Update query using an aggregate
funciton.

So say I have:

Record # 123:

Table Field A: 10
Table Field B: 5
Table Field C: 20

I want Table Field D to be Dmax(A, B, C). In this case I would want 20
stored in this field.

Is there any way I can run an update query, pull out the DMax of that
record, and store it in a field of the same record?

Thanks,

Chris

[Chris L.]

On Jul 25, 4:09 pm, wrldruler <[e-mail]:

Hello,

First, I know it's against "Access Law" to save calculations in a
table, but....I want/need to.

I currently have sub-totals being calculated inside a form, using
DMax, DCount, and DSum. But these aggregate functions are slowing down
the interface and annoying me. So I have decided not to show sub-
totals on the form.

But I still need the sub-total calculations performed so the sub-total
data can be used on other forms, reports, exports, etc.

I was thinking about doing a mass-update via query when the user
finishes an entry. So rather than having a pause everytime he enters a
number, I would just wait until the end, and mass-update.

But as you know, you can't create an Update query using an aggregate
funciton.

So say I have:

Record # 123:

Table Field A: 10
Table Field B: 5
Table Field C: 20

I want Table Field D to be Dmax(A, B, C). In this case I would want 20
stored in this field.

Is there any way I can run an update query, pull out the DMax of that
record, and store it in a field of the same record?

Thanks,

Chris

Update mytable set D = iif(a>=b, iif(a>=c, a, c), iif(b>=c, b, c ) )
where (a is not null and b is not null and c is not null);

Please try it, I haven't tested it... if you can't filter out null
values like I did, you'll most likely have to process them separately
(have a look at NZ function)

[Larry Linson]

"wrldruler" <fi******@hotmail.comwrote

First, I know it's against "Access Law" to
save calculations in a table, but....I want/need to.

You need to learn to be more precise. What you stated first is not so...
what is so is that you should not store in a Field in a Record a value that
can be calculated when needed from other Fields in the same Record. What you
stated second is only partly true: you may want to store the largest value
of three Fields in another Field in the same Record, but you definitely do
not _NEED_ to.

I currently have sub-totals being calculated inside
a form, using DMax, DCount, and DSum. But these
aggregate functions are slowing down the interface
and annoying me. So I have decided not to show sub-
totals on the form.

You may be using DMax, DCount, and DSum in calculations. If you think they
are working on values within the same Record, as is clear you expect for
DMax in a later statement here, then you are in for some surprises. DMax is
not the same as the MAX function from Excel, and DSum is not the same as the
SUM function from Excel. Why you would use a Count function on Fields that
you specify within the same record, I couldn't guess, so perhaps your later
statement is the one that's misleading.

But I still need the sub-total calculations performed
so the sub-total data can be used on other forms, reports,
exports, etc.

I was thinking about doing a mass-update via query
when the user finishes an entry. So rather than having
a pause everytime he enters a number, I would just
wait until the end, and mass-update.

But as you know, you can't create an Update query
using an aggregate funciton.

Yes, you can. It rarely makes any sense to do so, unless you are calculating
a percentage of the sum of all Fields in the database when the current Field
value is entered, or similar...

So say I have:

Record # 123:

Table Field A: 10
Table Field B: 5
Table Field C: 20

I want Table Field D to be Dmax(A, B, C).
In this case I would want 20 stored in this field.

Is there any way I can run an update query, pull
out the DMax of that record, and store it in a
field of the same record?

You can certainly use DMax in the Update To line of an Update Query, but it
certainly will not do what you clearly expect (which would be to act like
the MAX function in Excel).

I don't know what you expect from "DMax(A,B,C)" but if it is the maximum of
the three values A, B, and C in that record, you are going to be sadly
disappointed, and you really need to open a Module window, type in ' DMax,
then put the cursor on DMax, press F1 and read the Help. DMax returns the
largest value in the same Field from a domain specified in the statement.

And, almost certainly, if you have three values for the same "sort of thing"
as fields in your Table, and want to store the largest of these back in the
same Table, what you need to be doing is _not_ figuring out how to do that,
but taking a look at how you need to re-design your data and table layout.

But, if you feel compelled to start with a poor design, not realize you are
"in trouble and need to stop digging," and continue doing things that are
going to make your life miserable and more difficult as you continue, the
following will return the largest value of the three fields:

IIf(C (IIf(B A, B, A)), C, IIf(B A, B, A))

You should be able to use that in the Update To line of an Update Query to
set the D field. But, I warn you, you'll be sorrrrreeeeeeey.

Looks like you want to "commit spreadsheet" in your Access database, and
that's guaranteed to be a "losing proposition."

Larry Linson

[wrldruler]

Here is the full scope.

I've got a Project Resource Estimator. I've been asked to take an
existing Excel sheet, move it over to Access, and provide the same
look, feel, and functionality as the Excel sheet.

I am using a continuous subform. Estimator chooses a dropdown of
person type, then they say for month 1, how many people, what percent
of time, and the system calculates hours. They repeat that for 24
months. So they say:

I need an Operations manager. Month # 1: 1 person, 100% of the time,
so 147 hours. Month # 2: 2 people, 100% of their time, so 294 hours.

So for every record, I've got 3 textboxes * 24 months = 72 bound
textboxes spread across a continuous subform, using a tab control to
seperate into 6 month chunks.

I have sub-total boxes to the right of the tab control so they can see
how many hours they are consuming on each resource record. I have
built a seperate query and use aggregate functions to populate these
sub-totals on After_Update.

I use DMax("QTY","query_name", "record_ID =" & record_ID) which tells
me the max number of people they asked for in the months.

I use DCount to look at the number of non-zero, non-null fields in the
record, thus telling me how many months they need people for.

I use DSum to sum up all the month hours for the record.

The Grand Total was easy. I just dropped a textbox in the footer and
said Sum(Subtotal_Hours)

Form works fine, except the round trip to populate the sub-totals
takes about a second. And this pause is annoying when you're trying to
type numbers in.

So my question is how do I display a running total of say Sum(Hours)
for each record. In Excel, your data would be in cells A1 through X1,
and you would have a sub-total column in Y1, looking back over A
through X and doing Max, Sum, Count, etc. And I would think, with 24
fields, a nested IF statement would be nearly impossible.

And I am happy to hear recommendations on normalizing this table.

Thanks,

Chris

[Larry Linson]

The repeating fields are a violation of relational database design
principles, and are already causing you problems -- a better design would be
that you have a Record for each person or person-type, for each month. Then
you can do your calculations and to really show the results similarly to a
spreadsheet, use a Crosstab Query (or Pivot Table) to "turn the rows into
Columns".

Almost any use of columns to represent different time spans, however small
or large, is a "red flag" that you are violating the rule about repeating
fields.

I'd create a data entry form to allow entering the data for person either
for an individual month, or for a sequence of months, with one entry...
rather than to try to "simulate" the exact operation of a spreadsheet.

Larry Linson
Microsoft Access MVP

"wrldruler" <fi******@hotmail.comwrote in message
news:11*********************@b79g2000hse.googlegro ups.com...

>
Here is the full scope.

I've got a Project Resource Estimator. I've been asked to take an
existing Excel sheet, move it over to Access, and provide the same
look, feel, and functionality as the Excel sheet.

I am using a continuous subform. Estimator chooses a dropdown of
person type, then they say for month 1, how many people, what percent
of time, and the system calculates hours. They repeat that for 24
months. So they say:

I need an Operations manager. Month # 1: 1 person, 100% of the time,
so 147 hours. Month # 2: 2 people, 100% of their time, so 294 hours.

So for every record, I've got 3 textboxes * 24 months = 72 bound
textboxes spread across a continuous subform, using a tab control to
seperate into 6 month chunks.

I have sub-total boxes to the right of the tab control so they can see
how many hours they are consuming on each resource record. I have
built a seperate query and use aggregate functions to populate these
sub-totals on After_Update.

I use DMax("QTY","query_name", "record_ID =" & record_ID) which tells
me the max number of people they asked for in the months.

I use DCount to look at the number of non-zero, non-null fields in the
record, thus telling me how many months they need people for.

I use DSum to sum up all the month hours for the record.

The Grand Total was easy. I just dropped a textbox in the footer and
said Sum(Subtotal_Hours)

Form works fine, except the round trip to populate the sub-totals
takes about a second. And this pause is annoying when you're trying to
type numbers in.

So my question is how do I display a running total of say Sum(Hours)
for each record. In Excel, your data would be in cells A1 through X1,
and you would have a sub-total column in Y1, looking back over A
through X and doing Max, Sum, Count, etc. And I would think, with 24
fields, a nested IF statement would be nearly impossible.

And I am happy to hear recommendations on normalizing this table.

Thanks,

Chris