+ Reply to Thread
Results 1 to 10 of 10

Graphing in Access based on an ID number

  1. #1
    Forum Contributor
    Join Date
    08-14-2008
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    103

    Graphing in Access based on an ID number

    Hi all,

    I need to create graphs of data that is stored in an Access database. The data has following structure:

    Please Login or Register  to view this content.
    There are about 2500 different ID numbers, each with >100 rows (more data than excel can handle).

    I need to create a graph of Data vs. Date for each ID number (ideally printing to a PDF). I'm pretty inexperienced with Access but I imagine that the best way to do this would be using VBA? Does anyone know of a good way to do this?

  2. #2
    Registered User
    Join Date
    11-09-2008
    Location
    germany
    Posts
    74

    Re: Graphing in Access based on an ID number

    Access 2003 uses the MSGraph object library which is almost as good as the the library found in Excel. This means almost anything you can do in Excel and almost any VBA code for excel will work in Access.

    Thus your options would be to put a Graph on a new Access form and base the graph on your data as above. I am not that experienced with graphs as I have only done 4 in my life. But the way I do it is I create a special table just with your data and as you want it. Then the graph uses this special table for the source. I do this so that people can easily manipulate the table and thus change the graph dynamically.

    Secondly you could send the info (your table you have above) to Excel and let excel create the graph.

    The second option would allow you to easily turn it into an pdf.

  3. #3
    Forum Contributor
    Join Date
    08-14-2008
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: Graphing in Access based on an ID number

    Ok, thanks for the tip. I will look into making a form. My main problem is the volume of data. It is impracticle to make each graph individually, there are just too many!

    Quote Originally Posted by darbid View Post
    Secondly you could send the info (your table you have above) to Excel and let excel create the graph.

    The second option would allow you to easily turn it into an pdf.
    Unfortunatly there is far to many rows for excel (>400,000)

  4. #4
    Registered User
    Join Date
    11-09-2008
    Location
    germany
    Posts
    74

    Re: Graphing in Access based on an ID number

    So maybe I have not understood. I was kinda assuming that you would want a graph for one ID number at a time.

    What do you mean by each graph.

    I am thinking that while the data is kept in your mdb you can send a limited amount of data to excel to build the graph you want.

  5. #5
    Forum Contributor
    Join Date
    08-14-2008
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: Graphing in Access based on an ID number

    I was hopeing to make a graph for each and every ID number. Furthermore, it would be nice if these were all created in the same place (i.e. a single pdf).

    At the moment I have been exporting chunks of data to excel and working on those parts individually. However, this is cumbersome.

    ps. Sorry for my late reply I have been away

  6. #6
    Registered User
    Join Date
    11-09-2008
    Location
    germany
    Posts
    74

    Re: Graphing in Access based on an ID number

    Ok so in my opinion the best would be to create your graph in Excel then make a pdf of it. This would be looped for each ID.

    I am not going to give you the code to do it as it would take me a while to get it but I will give you the structure of how I would do it. You are welcome then to use this thread to ask specific questions on a specific piece of code you cannot get right. That way I also feel that you will learn as well.

    Your friend and mine is the macro recorder in excel. If you want to see how to do something then start a recorded macro and do it manually, then look at the code. This will give you an idea of how to do it.

    I am assuming that you are working in Access and have a normal access table.

    First you have to decide which records you want to graph. For example lets say you want to graph just one. The idea here is that myID could be a FOR loop or something else that feeds into your SQL every ID you want to graph.

    Please Login or Register  to view this content.
    Second now how are you going to get this to Excel. I would suggest you have a master excel file. On Sheet two set up a table like you want it. On Sheet one set up the graph like you want it based on the table.

    Here is how you open your excel master

    Please Login or Register  to view this content.
    Then what you are going to do is with the above code for each ID add the date and data to the existing table in excel. Then update the graph on the first sheet and then you will have your first graph. PDF this sheet, then loop to the next ID.


    This means it would be something like this.
    • Open your excel document
    • Open your database record set based on your first ID
    • Move to the first record in the database
    • Move all data for this record into your excel table
    • Update your graph based on this new table
    • pdf the table
    • move to the next rID
    Last edited by darbid; 02-16-2010 at 03:44 AM.

  7. #7
    Forum Contributor
    Join Date
    08-14-2008
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: Graphing in Access based on an ID number

    darbid,

    Wow! thanks for that. This is definitly at the edge of my ability in Access/Excel, but I will have a go. As you say, that's the best way to learn.

    Give me a few days to find some time to work on this. I will post here again if, or more likely, when I get stuck.

    Thanks again for your help

  8. #8
    Forum Contributor
    Join Date
    08-14-2008
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: Graphing in Access based on an ID number

    Ok first questions!

    1. Did you intend this code to be part of an Access Marco? Or is it to be run form Excel?
    2. Do I need to activate any particular references in VBA?
    3. Do you know if it is possible to print all the outputs to a single pdf? I'm trying to avoid making heaps of different files.

  9. #9
    Registered User
    Join Date
    11-09-2008
    Location
    germany
    Posts
    74

    Re: Graphing in Access based on an ID number

    Quote Originally Posted by HammerTime View Post
    1. Did you intend this code to be part of an Access Marco? Or is it to be run form Excel?
    I was thinking that this code would be on a form in MSAccess. You can control Excel, Word, Outlook from Access.

    Quote Originally Posted by HammerTime View Post
    2. Do I need to activate any particular references in VBA?
    Yes you will nee to make a reference in the VBEditor - Tools>References> Microsoft Excel 11.0 (or 12.0)

    Quote Originally Posted by HammerTime View Post
    3. Do you know if it is possible to print all the outputs to a single pdf? I'm trying to avoid making heaps of different files.
    I was thinking that you would create 1 graph, print to pdf then create the next. Thus you would have one PDF for each graph. To create one pdf of all graphs you would either need all the graphs "open" in a program at once to then print to pdf. That is probably not possible if you have many many graphs. The only other way I can think of doing it is that you would create the first pdf and then add each graph to the same pdf. This is something that I have never done, it would require referencing the Adobe COM library and then do it that way. Once again I am not 100% sure about this part as I have never done it.

  10. #10
    Forum Contributor
    Join Date
    08-14-2008
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: Graphing in Access based on an ID number

    darbid, once again thanks for the help,

    Work has been a bit crazy so I havn't been working on this, but hopefully will find the time soon

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1