473,706 Members | 2,328 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 4994
On Jul 25, 4:09 pm, wrldruler <filed...@hotma il.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******@hotma il.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","que ry_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_Ho urs)

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******@hotma il.comwrote in message
news:11******** *************@b 79g2000hse.goog legroups.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","que ry_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_Ho urs)

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
2256
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 3 6 9 I need to determine the mean of all results for each LotID, so used the Row Average function described on the Microsoft Knowledge base, which gives
10
11933
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 post) *I'm running a total query, of the form
2
1636
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 send round trip to the server. :) may be imposible :D
0
1414
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 is very big the group by takes along time and there is no need since it is unique. The second way is witha function which loops through each row in the first table and does the aggregate function for that row. Does anyone know of a way to do this...
1
2274
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 array. I need to use the text type because this function will be used on DNA sequences which can be very large. My questions are the following. What is the most efficient way to accumulate a text array while being efficient with memory? I see...
1
2727
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
1587
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.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
5
6096
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.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
2
4596
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 they be numbers this work good "trabajadoresTable.Compute("Sum(pago)", "")" But with the type DateTime's in a column make me the following error
0
8781
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9156
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9055
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7911
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6615
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5939
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4453
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3149
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2502
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.