creating a histogram query (1 Viewer)

JimL · Jul 28, 2019

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 · Jul 28, 2019

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 · Jul 29, 2019

research MS Access Partition function

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

MajP · Jul 29, 2019

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

Code:

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

Code:

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

Code:

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 · Jul 29, 2019

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.

Code:

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;

creating a histogram query (1 Viewer)

JimL

Registered User.

June7

AWF VIP

jdraw

Super Moderator

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread