Access equivalent to Excel Histogram/Bin?

[tubbers]

I've been tricked into helping with a new project and I'm not sure what I'm doing. The company I work for has an Excel spreadsheet that they are using to calculate information using the "Define..Table" and Histogram function. Unfortunately, there are too many records in their database and they want to create an Access database that will do the same.

I don't know much about Excel and even less about this Histogram/Binning that they used on their spreadsheet but have been asked to see what I could do in Access.

I was wondering if there was anyway of duplicating the histogram function in Access easily. I can provide an example of what I have in the way of the Spreadsheet if that would help.

Appreciate any help!

 

[dhookom]

Maybe you could provide a sample of what you mean by Histogram/Binning. Some of us that might be able to help, don't have a clue.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tubbers]

I couldn't figure out how to attach a file so I'll do my best to provide an example. I've uploaded an example spreadsheet to

http://www.geocities.com/tubbers76/ssheet_example.xls

This is probably going to be long and drawn out and hopelessly confusing. Sorry!

On the spreadsheet:

The first part (Columns A-Z) is set up as a Table (using Insert, Name, Define). The relevant fields needed for the calculation are:

JulianDate (values of 1 - 366)

Location (various text values, i.e. ARPHA, RRPHA, etc.)

Slot (values of 1 - 15)

FirstDetect (time/date field - example of values are "8:00:05", "11:03:42", etc.)

TotalHits (this is a sum of other a field range and is numeric)


The next section of the spreadsheet (Colums AE - AG)is a sort of form for data entry where the user would select the JulianDate and Location(s) for the calculation.

JulianDate (a value between 1 - 366)

Location (1): A value from the Location field which is categorized as a "manually tracked location", i.e. RRPHA.

Location (2): A value from the Location field which is categorized as a "auto tracked location", i.e. ARPHA.

The point of the spreadsheet (I believe) is a comparison between the TotalHits of Location 1 (manually tracked) and the TotalHits Location 2 (auto-tracked) for every hour on a specific JulianDate and for a specific Slot.

If you select 124 for the Julian Date (Column AE) and RRPHB for Location 1 (Column AF) and ARPHB for Location 2 (Column AG), then you can see the results that appear in the next section.

The next section over (Colums AI - GN) is where the results of the "query" are shown. I believe this is the section where the Histogram function is applied.

I'm not really sure how to explain how it is layed out.

Does this example help you understand what I'm trying to do? I don't really understand it myself so its hard to explain.

Thanks

 

[Crowley16]

not sure exactly what you're trying to achieve...

why don't you try posting in the office/excel forum to find out exactly what those functions do?

 

[scriverb]

These functions are statistical data analysis in nature. The specifics of how you are using them in your spreadsheet would have to been analyzed to duplicate it in ACCESS. This would require multiple individual queries to try to duplicate what already have.

The link provided for your sample spreadsheet does not work. Says the page is not available.

Seems like you would have to dig into the details of your spreadsheet and determine just what is being analyzed and what is needed. Then post back with very specific needs for anyone here to help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[tubbers]

Okay, I'll see if I can get further clarification of what it is the spreadsheet does.

Thanks.

 

[MichaelRed]

Well, I can access & retrieve the spredsheet of interest. It does not, however, provide any direct enligthtenment re the issue of your implementation of the "Histogram".

Generically, "Histogram" refers to a bar (Column) Chart. Unless you have some thrid party software, both Ms. A, and Ms. Excel both use the MS Chart "Applet" as their plotting / charting engine, so part of the issue is easily resolved. Unfortunatly, the interfaces to Ms. Chart are (or at least were a while ago) somewhat different, so it is not completly solved, and may require some considerable restructuring in the creation of the chart.

Another potential issue is that Ms. A. can (will?) not export the chart itself to any other app. This limitation is a bit arcane / weird, but associated with the lack of support for graphics in the export.

In several instances, I have resorted to doing the opposite, creating a spreadsheet with the charting process embeded and simply replacing the data for the chart for the purposes of sending the "Report" to users.

p.s. the reason there is no specific reference to histogram in your spreadsheet may be that there are no modules; macros; or scripts, at least that I could find. Recalculating hteeSpreadsheet also did not appear to generate any activity.




MichaelRed
mlred@verizon.net

 

[THWatson]

Not sure if it would be helpful in your situation, but there is an example of a Histogram construction at BlueClaw. The link is

http://www.blueclaw-db.com/microsoft_access_histogram.htm

Tom

 

[tubbers]

I've checked out that BlueClaw example but it seems like my situation is a bit more complicated.

Anyway, I've been pulled off this project for the time being since we're nearing a deadline and I have tags to verify but will more than likely come back to this in a week or two.

Thanks for the help.