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


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 ..
LAPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KostaCommented:
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
LAPAuthor Commented:

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
TrygveCommented:
"Bin and Frequency". What are these?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LAPAuthor Commented:

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
LAPAuthor Commented:


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
KostaCommented:
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
TrygveCommented:
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
LAPAuthor Commented:

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
LAPAuthor Commented:
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
TrygveCommented:
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
LAPAuthor Commented:

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
TrygveCommented:
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
LAPAuthor Commented:

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
TrygveCommented:
I will have a look at it first thing tomorrow.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LAPAuthor Commented:

Trygve,

Thank you for your expertise.

It works perfectly.

Regards,
LAP
0
TrygveCommented:
HTH

Good luck on your project!
Trygve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.