Prev: Output Query as an Excel file
Next: start date & end date - show all date in between
From: JD on 10 May 2010 20:12
My intent is to add a field to my table that contains the cumulative
percentage of the CumFreq field (cumulative frequency). Any help would be
greatly appreciated. My source table 'AllinOne' looks like this:

Speed,Frequency_Vehicles,CumFreq,WeekNum
10,1,1,31
13,1,2,31
14,1,3,31
17,3,6,31
20,1,7,31
Etc, etc.

Thanks


From: Bob Barrows on 10 May 2010 20:25
JD wrote:
> My intent is to add a field to my table that contains the cumulative
> percentage of the CumFreq field (cumulative frequency). Any help
> would be greatly appreciated. My source table 'AllinOne' looks like
> this:
>
> Speed,Frequency_Vehicles,CumFreq,WeekNum
> 10,1,1,31
> 13,1,2,31
> 14,1,3,31
> 17,3,6,31
> 20,1,7,31
> Etc, etc.
>
> Thanks

Please show us what the results need to look like.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: John W. Vinson on 10 May 2010 20:44
On Mon, 10 May 2010 17:12:04 -0700, JD <JD(a)discussions.microsoft.com> wrote:

>My intent is to add a field to my table that contains the cumulative
>percentage of the CumFreq field (cumulative frequency). Any help would be
>greatly appreciated. My source table �AllinOne� looks like this:
>
>Speed,Frequency_Vehicles,CumFreq,WeekNum
>10,1,1,31
>13,1,2,31
>14,1,3,31
>17,3,6,31
>20,1,7,31
>Etc, etc.
>
>Thanks
>

YOu really, really do NOT want to add this calculated field to your table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.

--

John W. Vinson [MVP]
From: John Spencer on 11 May 2010 09:14
How do you intend to calculate the Cumulative Percentage?

CumFreq divided by SOME NUMBER. What is that number? How is it calculated?

As advised elsewhere it is not a good idea to store this in the table in most
cases as it will be subject to being wrong if you modify any record.

Also what defines the order of the records that defines the accumulation?
Looking at your sample data I would guess that the Speed field (and perhaps)
the WeekNum field) may define the order.

Also I note that CumFreq is another value that should not normally be saved in
the table since it is again subject to being wrong if you modify any record.

For instance if you find that the first record should have been entered as
10,2,2,31
and fix that then all the subsequent records would have to be edited.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

JD wrote:
> My intent is to add a field to my table that contains the cumulative
> percentage of the CumFreq field (cumulative frequency). Any help would be
> greatly appreciated. My source table 'AllinOne' looks like this:
>
> Speed,Frequency_Vehicles,CumFreq,WeekNum
> 10,1,1,31
> 13,1,2,31
> 14,1,3,31
> 17,3,6,31
> 20,1,7,31
> Etc, etc.
>
> Thanks
>
>
 | 
Pages: 1
Prev: Output Query as an Excel file
Next: start date & end date - show all date in between