473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Perform Mass Aggregate Function Calculation in Table

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

Jul 25 '07 #1
4 4963
On Jul 25, 4:09 pm, wrldruler <filed...@hotmail.comwrote:
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)

Jul 25 '07 #2
"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
Jul 25 '07 #3

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

Jul 26 '07 #4
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

Jul 26 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: sausage31 | last post by:
I have a table as follows.... Device LotID Result1 Result2 Result3 aaa 1 5 10 15 bbb 1 2 4 6 aaa 2 ...
10
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
2
by: kids_pro | last post by:
Hi there, I think it is a stupid idea but I wonder since dataset is a disconnected datasource. Why not all some more functionality to it like SUM, AVG, MIN, MAX when? So that we don't need to...
0
by: Matthew Lunnon | last post by:
Hi, I have two tables and I want to get summary information from the second table for each row of the first table, I can see two ways to do this one is with the SQL below but since the first table...
1
by: Joel Dudley | last post by:
Hello, I am about to write a set of C functions to be used in an aggregate function in which the final function performs a calculation on an array of accumulated text data types stored in a text...
1
by: Najib Abi Fadel | last post by:
Hi i have an ordered table of dates let's say: 1/1/2004 8/1/2004 15/1/2004 29/1/2004 5/2/2004 12/2/2004
0
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
5
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
2
by: edcha | last post by:
I am trying to use the following Aggregate Calculation. string time = trabajadoresTable.Compute ( "Sum(DateTime)" , "") .ToString(); I need to sum the hours in a column with type DateTime. If...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.