creating a histogram query

[JimL]

Good evening all,

I have scoured sources and not been able to find how to set up simple histogram queries.

Can anyone shed some light, and perhaps a sample database?

THanks

JimL

 

[June7]

Review http://blueclaw-db.com/accessquerysql/histogram.htm

Perhaps a CROSSTAB query is what you want http://allenbrowne.com/ser-67.html

Provide sample data and desired output.

 

[jdraw]

research MS Access Partition function

Here is one link with example.
Another from M$oft

 

[MajP]

I built a table called tblHistogram
tblHistoGram
BinStart
BinEnd
NumberObservations

I can run the following code on any numeric field by passing the name of the table and the name of the field

Public Sub CreateHistogram(TableName As String, FieldName As String, Optional Bins As Integer = -1)
  Dim min As Double
  Dim max As Double
  Dim range As Double
  Dim observations As Long
  Dim inc As Double
  Dim i As Double
  Dim strSql As String
  Dim binStart As Double
  Dim ObsInBin As Long
  min = DMin(FieldName, TableName)
  max = DMax(FieldName, TableName)
  max = 1.05 * max
  range = max - min
  
  
  If Bins = -1 Then
    observations = DCount("*", TableName, FieldName & " is not Null")
    Bins = 1 + 3.22 * Log(observations)
  End If
 
  inc = range / Bins
   Debug.Print "Bins " & Bins & " Range " & range & " obs " & observations & " inc " & inc
  strSql = "delete * from tblHistogram"
  CurrentDb.Execute strSql
  binStart = min
  For i = (min + inc) To max Step inc
    ObsInBin = DCount("*", TableName, FieldName & " between " & binStart & " AND " & i)
    Debug.Print "start " & binStart & " end " & i & " obs " & ObsInBin
    strSql = "Insert into tblHistogram (BinStart, BinEnd, NumberObservations) values (" & binStart & ", " & i & ", " & ObsInBin & ")"
    CurrentDb.Execute strSql
    binStart = i
  Next i

End Sub

You would call it like

Public Sub TestIt()
  CreateHistogram "HistoData", "RanNumber", 10
End Sub

where histodata is the table with a numeric data column and RanNumber is the name of the field.
The output is

BinStart	BinEnd	NumberObservations
0	104.79	111
104.79	209.58	115
209.58	314.37	122
314.37	419.16	100
419.16	523.95	93
523.95	628.74	110
628.74	733.53	96
733.53	838.32	88
838.32	943.11	98
943.11	1047.9	67

The histdata tables has 1k observations with values from 0 to 998. It is not bulletproof and may need some tweaks. But you can call other tables and other fields and it will clear out the table.

 

[MajP]

Also you can define your own bins and put them in a table. It can be done without a start stop, but easier to do it that way.

SELECT tbldefinedbins.binstart, 
       tbldefinedbins.binend, 
       Count(histodata.rannumber) AS CountOfRanNumber 
FROM   histodata, 
       tbldefinedbins 
WHERE  (( ( histodata.rannumber ) BETWEEN [binstart] AND [binend] )) 
GROUP  BY tbldefinedbins.binstart, 
          tbldefinedbins.binend 
ORDER  BY tbldefinedbins.binstart, 
          tbldefinedbins.binend;

 

[EricRex]

Also you can define your own bins and put them in a table. It can be done without a start stop, but easier to do it that way.

SELECT tbldefinedbins.binstart,
       tbldefinedbins.binend,
       Count(histodata.rannumber) AS CountOfRanNumber
FROM   histodata,
       tbldefinedbins
WHERE  (( ( histodata.rannumber ) BETWEEN [binstart] AND [binend] ))
GROUP  BY tbldefinedbins.binstart,
          tbldefinedbins.binend
ORDER  BY tbldefinedbins.binstart,
          tbldefinedbins.binend;

Years later, thank you for this. I tried a lot of other googled ideas, but none of them worked. Yours does!