?
Solved

Getting Bin and Frequency values from data in a query to generate a Histogram.

Posted on 1999-11-07
16
Medium Priority
?
425 Views
Last Modified: 2008-03-06

I have developed an application in MS Access 97. I have a query containing data. I need to get the Bin and Frequency values of this data. Once I have the Bin and Frequency values, I need to plot this information in a Histogram (graph).

I tried automating this in MS Excel 97. The data in the MS Access query is Transfered to a Spreadsheet using the TransferSpreadsheet action in the macro.
I then generate the Histogram in MS Excel 97.

I get the desired results but each time my data is updated in MS Access the Histogram (in MS Excel) is not updated. (The data in the worksheet is updated,though)

How would I determine the Bin and Freqency values in MS Access 97? Once I have these values how do I generate a Histogram using the Microsoft Graph 97 Chart?

Please help ..
0
Comment
Question by:LAP
  • 8
  • 6
  • 2
16 Comments
 
LVL 6

Expert Comment

by:Kosta
ID: 2192020
There is a wery simple way to make a graph in Access. Create a report, select Insert/Chart from the menu, and follow the wizard.
0
 

Author Comment

by:LAP
ID: 2193123

Hi Kosta

My problem lies with getting the Bin and Frequency values for the data in my query.
I have worked with the Chart wizard before so I know how to draw the graph.

Thanks,
LAP
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2193196
"Bin and Frequency". What are these?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:LAP
ID: 2193217

Bin and Frequency values are used in MS Excel 97. How it works in MS Excel is that you have a data range. In my case, the data of my MS Access 97 query is my data range.

Back to MS Excel ...
now when you draw up your Histogram you specify the data range and MS Excel automatically specifies the Bin range (the numbers to look for in the data range). The Frequency is plotted on the Histogram. The Frequency is the number of times the number specified in the Bin appears in the data range.

Hope this makes sense. For further clarification look in the Help of MS Excel 97.
0
 

Author Comment

by:LAP
ID: 2193226


Example
=======

Data range:  1 5 9 5 9 3 2 2 9 9

Bin: 1 2 3 4 5 6 7 8 9


Frequency: 1 2 1 0 2 0 0 0 4  (i.e. the number of times the number 1, etc. appears in the data range)
0
 
LVL 6

Expert Comment

by:Kosta
ID: 2193245
I whould use an OLE Autometion. Open Excel as an OLE Autometion server for Access, let him get the data from the Access query as an external data. Then let him build or refresh the graph.

Kosta.
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2193285
You could use a group table.

Will the bin always contain adjacent numbers (1,2,3,4...)?

If so, you can make a table with all the numbers from 1 to the highest number you thing you will need.

My setting:

ExcelBinTable with BinValue a numeric field.

ExcelData with Value a numeric field which contains you data range.


Then this query gives the result you want.

SELECT ExcelBinTable.BinValue, Count(ExcelData.ID) AS CountOfID
FROM ExcelBinTable LEFT JOIN ExcelData ON ExcelBinTable.BinValue = ExcelData.Value
WHERE (((ExcelBinTable.BinValue)<=DMax("[Value]","ExcelData")))
GROUP BY ExcelBinTable.BinValue;

The where part is to limit the set of BinValues.
0
 

Author Comment

by:LAP
ID: 2193288

I've created the following macro in MS Excel in the VBA Editor.

Sub GenerateHistogram()
Dim c As Worksheet

tStr = Application.Path & "\Library\Analysis\ATPVBAEN.XLA"
Workbooks.Open tStr

Application.DisplayAlerts = False

For Each c In ThisWorkbook.Worksheets
    If StrComp(c.Name, "Hist") = 0 Then
       c.Delete
    Exit For
    End If
Next

Application.DisplayAlerts = True
   
     Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$D$2:$O$32"), _
        "Hist", ActiveSheet.Range("$A$34:$A$48"), False, False, True, False

End Sub

The macro generates a Histogram from the data range exported from MS Access.

I get the following error msg when I run the macro:

Run-time error '1004'
'atpvbaen.xls' could not be found.  

When I click on the Debug button it points to the following line of code:

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$D$2:$O$32"), _
        "Hist", ActiveSheet.Range("$A$34:$A$48"), False, False, True, False

The atpvbaen.xla file is in the correct location on my PC. I also see the .xls file in the VBA editor.


I don't have a clue as to where the error could be. Therefore I thought about doing the whole thing in MS Access.
0
 

Author Comment

by:LAP
ID: 2193795
Trygve,

I've tried your code and it works perfectly! My problem is more complexed though.

Instead of one field (like your Value field)to evaluate I have 12 fields. Of this data range the query should count how many of the values (of the data range) fall within the following rules:

>-0.5 <=0.00
>0.00 <=0.25
>0.25 <=0.5
>0.50 <=0.75
>0.75 <=1.00
>1.00 <=1.25
>1.25 <=1.50
>1.50 <=1.75
>1.75 <=2.00
>2.00 <=2.25
>2.25 <=2.50
>2.50 <=2.75
>2.75 <=3.00
>3.00 <=3.25
>3.25 <=3.50
>3.50

How can this be achieved?
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2194024
I would need to see the tables etc. to not spend too much time on it.

Is it possible for you to send this to me (Trygve@Omega.no)?

I think it should be possible with a bit more complicated queries.
0
 

Author Comment

by:LAP
ID: 2196086

I've e-mailed the database to you with
"MS Access 97  : Q.10230427" in the Subject:.

If there are any queries please ask.

Thank you for your assistance thus far.

Regards,
LAP
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2196371
Sorry about the slow mind, but how do you want this formatted. A list like this

>-0.5 <=0.00   TotalCountForAllColumns.

Or
Per Date?
Per LocoNo?
Per Column?

I also got an error from your EMail address. datavia.co.za does not appear to be a valid DSN?
0
 

Author Comment

by:LAP
ID: 2196863

Trygve,

TotalCountForAllColumns is the one.

Our e-mail has been up to ..... (fill in the blanks).

That is a valid DSN.
Alternatively, try this e-mail address: Leigh_Annep@hotmail.com

Thanks!
0
 
LVL 12

Accepted Solution

by:
Trygve earned 400 total points
ID: 2198494
I will have a look at it first thing tomorrow.
0
 

Author Comment

by:LAP
ID: 2202255

Trygve,

Thank you for your expertise.

It works perfectly.

Regards,
LAP
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2202279
HTH

Good luck on your project!
Trygve
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses
Course of the Month3 days, 9 hours left to enroll

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question