percentage of counts

[Guest]

I have a database with 14,000 records (trips) showing time duration for
journey to work (JTW).
Range of times is from 1-298 minutes. I need to first group the time
durations and then I need a show those time durantions as a percentage of
Grand total of records(trips). For example using a smaller group - If I have
24 records and 8 records show a time duration of 22 minutes, 6 records show
12 minutes, 4 records show 10 minutes and 6 records show 5 minutes. That
would mean that approximately 33% of records show a JTW time duration of 22
minutes, 25% show JTW of 12 minutes, 15% show 10 minutes and another 25% show
5 minutes.

Please advise.

Thanks

 

[Bob Quintal]

I have a database with 14,000 records (trips) showing time
duration for journey to work (JTW).
Range of times is from 1-298 minutes. I need to first group
the time durations and then I need a show those time
durantions as a percentage of Grand total of records(trips).
For example using a smaller group - If I have 24 records and 8
records show a time duration of 22 minutes, 6 records show 12
minutes, 4 records show 10 minutes and 6 records show 5
minutes. That would mean that approximately 33% of records
show a JTW time duration of 22 minutes, 25% show JTW of 12
minutes, 15% show 10 minutes and another 25% show 5 minutes.

Please advise.

Thanks

Create a new query. Bring down the JTW field twice. Click on the
Sigma in the toolbar to summarize the results. Change the Group
By to Count under the second column. Sort On the group by
column.

When you run this query you'll get the JTW minutes and the
number of trips with that JTW value. Set up a chart with the
durations along the X axis and the count in Y. verify that you
have a reasonable bell curve. If you do not have a good curve,
your data won't mean much statistically.

If it's too flat, you might be wise to group say every 5 minutes
by getting the integer portion of JTW\5 (note the \ means return
the integer portion only) then multiplying by 5-- TripTime: (JTW
\5)*5

Now edit the query, In the Count column, divide the count column
by a total_count. You can get the total count from a summary
query with only the one column, count([JTW]), or from a DCount
("JTW","Tablename"). Format that as a percentage.

 

[James A. Fortune]

I have a database with 14,000 records (trips) showing time duration for
journey to work (JTW).
Range of times is from 1-298 minutes. I need to first group the time
durations and then I need a show those time durantions as a percentage of
Grand total of records(trips). For example using a smaller group - If I have
24 records and 8 records show a time duration of 22 minutes, 6 records show
12 minutes, 4 records show 10 minutes and 6 records show 5 minutes. That
would mean that approximately 33% of records show a JTW time duration of 22
minutes, 25% show JTW of 12 minutes, 15% show 10 minutes and another 25% show
5 minutes.

Please advise.

Thanks

Try my experimental histogram program:

http://www.oakland.edu/~fortune/Histogram.zip

You would need to change the program to obtain and print the summary
percentages you need. Maybe use 298 class intervals to get fine details
(usually 5 to 20 class intervals suffice). Currently, you pick a table
and a (numeric) field then click 'Analysis.' It'll show the min, max
and count of the data, then let you select the min value, max value and
number of class intervals you want to show on the histogram. It creates
a pdf file which can be opened with, say, Acrobat Reader. I'd post more
information, but lots of my comp.databases.ms-access posts have suddenly
disappeared from Google Search. Actually, they show up in Google Search
but when I click on them they no longer show in the thread. There may
be an easy way to do this in SQL, but nothing communicates a
distribution as well as a graph.

James A. Fortune
(e-mail address removed)

 

[Al Campagna]

I would certainly consider grouping your calculations. It sounds like
you want a Percentage calculation for each possible Duration from 1 to 298?
I would think that would require 298 calculations... or 298 groups, each
with their own Group%.

Maybe something like a Group20 would sum/count any durations bewteen 1
and 20? And Group40 for 21-40...etc...?

Really need to know a bit more about your Grouping and calculating. Best
would be some sample data with your expected calculations, and arranged as
you want.

But... anywho....
Using your Duration example of 8 - 22 durations.
An unbound text control with a ControlSource of...
= (Sum(IIF(Duration = 22, 1, 0)) / Count(Duration))
would give a report footer calculation. (8 / 24 = .33 or 33%)

Depends on how you Group and Sort the report as to the actual Duration
calculation.

--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."