Frequency Distribution in Access

  • Thread starter Thread starter w945106
  • Start date Start date
W

w945106

I have a large table with three variables:
Provider PatientID# PatientAge

I want a patient age frequency distribution for each provider (there are
12 providers).

How do I do this with a query or report?

Thanks,

Jeff
w945106@hotmail.~~~
 
Do you want to group by each year or ranges or years? Do you have a desired
output? In its simplest form
SELECT Provider, PatientAge, Count(PatientID) as NumOf
FROM tblLargeTable
GROUP BY Provider, PatinetAge;
 
Duane said:
Do you want to group by each year or ranges or years? Do you have a desired
output? In its simplest form
SELECT Provider, PatientAge, Count(PatientID) as NumOf
FROM tblLargeTable
GROUP BY Provider, PatinetAge;
Click to expand...
For the whole group of 12 "providers", I made this frequency
distribution in Excel:

Age Freq
< 1 mo. 398
1 - 2 mo. 206
2 - 3 mo. 189
4 - 6 mo. 461
7 - 12 mo. 1123
12 - 23 mo. 2211
2 - 4 yrs. 4674
5 - 11 yrs. 4854
12 - 20 yrs. 2335
21+ yrs. 679


Now I want to break this down to find out how many patients of each age
group were cared for by EACH provider. Too much hassle in Excel, so I
am trying to do it in Access.

My large table looks like this:

Patient AGE (months) BProv.
74177 0.00 GR
76473 0.00 VU
75397 0.00 DV
78958 0.00 GA
79299 0.00 JM
83593 0.00 LR
82451 0.00 VU
87634 0.00 MG
85765 0.00 LV
86739 0.00 GR
86311 0.00 FM
88886 0.00 JM
89504 0.00 MG
91160 0.00 MG
92274 0.00 VU
92758 0.00 GR
94175 0.00 LV
96405 0.00 JM
96878 0.00 JE
73721 0.03 VU
74174 0.03 GA
74863 0.03 GV
76795 0.03 DV
77450 0.03 VU
78583 0.03 GR
79973 0.03 LR
80868 0.03 LR
81496 0.03 GA
81776 0.03 GA
82026 0.03 GR
85166 0.03 JE
85665 0.03 JV
85911 0.03 JE
86064 0.03 JV
86119 0.03 FM
86700 0.03 RD
88158 0.03 DV
88711 0.03 JV
89510 0.03 LR
90405 0.03 MG
91764 0.03 LR
92982 0.03 SL
92869 0.03 GA
92839 0.03 FM
92839 0.03 LV
94391 0.03 MG
95153 0.03 JE
95535 0.03 GRV
95535 0.03 VU
95967 0.03 FM
96119 0.03 DV
96481 0.03 JM
97148 0.03 GR
73765 0.07 GR
74204 0.07 JM
74881 0.07 JV
and so for 17000 rows.

Jeff
 
The first requirement is to create a table of Min and Max months for each
age group.

MinMths MaxMths Title
0 1 "< 1 mth"
1 2 "1-2 mths"
etc.

You still haven't shown us how you expect the results to look for each
Provider. Do you expect to see all age groups for ProviderA then all for
ProviderB,....
 
Duane said:
The first requirement is to create a table of Min and Max months for each
age group.

MinMths MaxMths Title
0 1 "< 1 mth"
1 2 "1-2 mths"
etc.

You still haven't shown us how you expect the results to look for each
Provider. Do you expect to see all age groups for ProviderA then all for
ProviderB,....
Click to expand...

I did it in Excel. But I would still like to know how to do it in
Access. I am sure it would have been easier.

Bin DV FM GA GR
< 1 mo. 0.999 35 36 24 34
1 - 2 mo. 1.999 21 12 19 12
2 - 3 mo. 2.999 10 16 18 17
4 - 6 mo. 5.999 32 37 28 34
7 - 12 mo. 11.999 80 78 94 78
12 - 23 mo. 23.999 165 152 226 169
2 - 4 yrs. 59.999 366 388 465 407
5 - 11 yrs. 143.999 371 406 528 354
12 - 20 yrs. 251.999 208 161 196 196
21+ yrs. More 66 46 55 56
1354 1332 1653 1357

Thanks,

Jeff
 
You can add your lookup table of age groups to a query with your
"LargeTable"
Set the criteria under [Age] to
=[MinMths] And <[MaxMths]
Click to expand...

Then change this into a crosstab query with Title as the Row Heading, BProv
as the Column Heading and Count(Patient) as the value.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You may wish to examine the Partition() VBA function. It can be used
in the query to get an equal age distribution. The Age column can
only be one measurement type (days or months or years - not a
mixture).

Syntax:

Partition(number, start, stop, interval)

E.g.: Partition(Age, 0, 21, 5)

This would show a partition of 5 (years, months, days ?) for all Ages
between 0 and 21. If any Ages are outside the range of 0-21 they will
be marked as :0 for < 0 and 21: for > 21.

See the Access VBA Help article on Partition Function for more info.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQC7+CYechKqOuFEgEQLGSQCePS37F7T/ia8azU3X1NdTfmqcm1UAoJg8
Nyl5X+ZSSS+Yfg4RUnuU99NR
=hFzD
-----END PGP SIGNATURE-----
 
The three issues I see with Partition():
-The groups must all be the same duration
-There is little or no control over the group titles
-It is difficult to give users control over the increments etc.
 
Back
Top