Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Unanswered: Histograms in Access

    Hi,
    I'm trying to come up with a flexible method of creating frequency tables (and charts) for data in accesss.

    For example, given a table with two fields (Items, Purchase Price). I'd like to determine and chart the number of purchases from $0-$10, $10-$20 etc...

    I can do this in excel but that option doesn't give me the flexibility I require (although I may need to ifnd an automation solution). I'd like to try and stay in excel.

    Following the guidance here
    http://www.blueclaw-db.com/accessquerysql/histogram.htm
    I developed some basic code that generates sql to give me a table with fields
    (group1, group2, group3, group4...) etc. Which gives me the data I require (counts of items in each group) but cannot be graphed do to access only allowing a maximum of 6 fields in a chart. I really need more reolution than 6 bins (groups) provides.

    So, I've been struggling with trying to figure out how to genearate a query that results in a data table with two fields (Group, Count). Where [group] is the text label of the bin i.e. "$0-$10" and count is the number of items in that group.

    Any suggestions, help, or encouragement would be greatly appreciated.

    Tim Sweeney

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Just for first look, can you download the excel automation code in the code bank above this thread and see if the graph producing functionality is likely to get you some way there? If not we can rule it out.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tsweeney
    Any .... encouragement would be greatly appreciated.
    Yay! Go Tim!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Posts
    10
    Thanks,
    I actually downloaded the automation stuff this morning. I'll take a look @ it this afternoon and get back to the thread tommorow. My only concern is that I really just want this for a flexible look at the data not for reports and thus would just be using excel for a front end, a big bloated, chart maker. It very well may be the best option though. I'll report back.

    T

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok then

    Quote Originally Posted by tsweeney
    So, I've been struggling with trying to figure out how to genearate a query that results in a data table with two fields (Group, Count). Where [group] is the text label of the bin i.e. "$0-$10" and count is the number of items in that group.
    This can be done reasonably flexibly with SQL. Let me know if you need to know as it takes a little typing :-)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is run your sql to generate the grouped data, that woudl eb the first issue

    ok so its going to have a value or range of values and a count.
    its going to be something select count(<myvalue>) as <myalias> from <mytable> group by <myvalue>

    having done that the problem is how do you display it, which si the second part.

    there may well be a stock chart within Access that you can use to represent that data, but if there isn't then its perfectly possible to create a barchart or histogram type chart within an access report. The pie chart variant has alwasy flummoxed me todate, but bar charts can be done in access (you 'just' adjust the height or width of some predefined boxes, adjust the z layer and jobsagooldun.

    you are not tied to using the bundled MSCharting object if you don't want to or it doesn't do what you want. The only thing I would say is that it can require a fair bit of VBA programming in the background.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Posts
    10
    Thanks for the suggestions guys. I'll try them out. The problem the sql you posted, healdem is that it will group on specific value and not ranges. I'm not trying to count specific values but rather ones that fit into certain size bins.
    Perhaps I'm missing something though so I'll give it a try.

    Your advice about trying to chart in a report or looking at other charting means is well taken. I will explore further.

    Thanks Again,
    Tim Sweeney

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so be a bit creative in your sql
    use a sub query to assign a range ID and group on that Range ID in the main query.

    one of the problems of dealing with stuff in the computer wolrd is that there is usually a way to do "something", oftne theres multiple wasy do get the same end result... its a matter of finding an appropriate route for you.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Posts
    10
    Just a quick update. I've got a solution working via access automation. It's definitely workable. But perhaps not all that elegant. I'm going to try to get a query based solution working and will report back.

    Tim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •