Frequency Distribution

[mmasuda]

Is it possible to use data from MS Access and form frequency distribution tables?
If so, how?

 

[67peshawar294]

mmasuda,
I did this several years ago and have forgotten exactly how I did it. I hope you can make some sense out of this and that it helps.
jim

Table: FRAMES

Fields: Whpr,Price,nhere

FormName:WHPR_PRICERANGE
Record Source for form:
SELECT DISTINCTROW Partition([whPR],0,300,Price) AS [WhPRICE Range], Sum(FRAMES.nhere) AS nhere FROM FRAMES GROUP BY Partition([whPR],0,300,Price);

In the detail section of the form there are three txtboxes:
[whpricerange],[nhere]and a calculated txtbox
=[NHERE]/Sum([NHERE])
In the FormFooter, a txtbox:=sum([nhere])

I also have a command button to enter a different price to see the distribution for any dollar amount.

Option Compare Database
Option Explicit

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

    Dim stDocName As String

    stDocName = "WHPR_PRICERANGE"
   DoCmd.Close acForm, "WHPR_PRICERANGE"
   
DoCmd.OpenForm stDocName, acNormal, acEdit

Exit_Command7_Click:
    Exit Sub

Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click
    
End Sub

The result for $12 is (sorry they are not perfectly lined up):
WhPRICE Nhere %NHERE TotalofNhere
0: 11 23 2.86% 803
12: 23 105 13.08% 803
24: 35 69 8.59% 803
36: 47 144 17.93% 803
48: 59 160 19.93% 803
60: 71 121 15.07% 803
72: 83 72 8.97% 803
84: 95 52 6.48% 803
96:107 30 3.74% 803
108:119 9 1.12% 803
120:131 4 0.50% 803
132:143 1 0.12% 803
144:155 2 0.25% 803
156:167 2 0.25% 803
168:179 8 1.00% 803
216:227 1 0.12% 803
264:275 0 0.00% 803
The result for $25 is:
WhPRICE nhere %NHERE total
0: 24 132 16.44% 803
25: 49 224 27.90% 803
50: 74 285 35.49% 803
75: 99 115 14.32% 803
100:124 31 3.86% 803
125:149 3 0.37% 803
150:174 9 1.12% 803
175:199 3 0.37% 803
225:249 1 0.12% 803
250:274 0 0.00% 803

 

[MichaelRed]

see the ubiquitous {F1} )aka H E L P, key word "partition"





MichaelRed