Results 1 to 4 of 4
  1. #1
    kevinr33 is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    2

    Need My Form To Look Through Make Table Query & Output Graph Plus Printing Graph Opti

    Hi.. Thanx to all in advance for taking the time to read & attempt to solve my problem

    I have a make table query that lists a 100 countries and the average rain precipitation for each month of this year and the previous year.
    An example of what the table and graph look like have been attached in the excel file. Note that the numbers are just random nos.

    I need to create a form where a user can choose a country from a drop-down box and after clicking a button, it shows a line graph plotting two lines for the 2 years for each month like a time-series. I also need to add 2 print buttons on the form; one button to print the user inputted country's graph, and another PRINT ALL button, that automatically prints the graphs for all 100 countries. FYI.. I've created the form already and have a show button, which when clicked on goes to a query which calls the make table, and opens it in pivotchart view. If this method is not right to answer my following questions, plz explain if there's another option to get the graph allowing to print it.

    Can anyone help in creating this drop-down menu with a list of the countries if I have the countries in the make table, TBL_CNTRIES? Also, how do I go about the 2 print options, especially the PRINT ALL button?? If I need to write a module for this, what would it look like?? Any help would be appreciated.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    I'm not sure why you are using a make table query. But before worrying about the forms, you need to create a normalized table structure. If you just bring in the excel data, it will not be normalized.

    I would first create the normalized table structure that will receive the data and then use append queries to get the data into that table structure.

    You will need a table to capture the countries

    tblCountry
    -pkCountryID primary key, autonumber
    -txtCountryName

    tblPrecipData
    -pkPrecipDataID primary key, autonumber
    -fkCountryID foreign key to tblCountry
    -dtePeriodEnding (ending date for the precipitation period i.e. 4/30/2009 etc.)
    -precipitationvalue
    -fkUOMID foreign key to tblUnitsOfMeasure

    tblUnitsOfMeasure (not sure if you need this table but if your datavalues can be entered in multiple units of measure then I would recommend it as well as the fkUOMID in tblPrecipData)
    -pkUOMID primary key, autonumber
    -txtUOM


    You can then base a combo box on tblCountries

  3. #3
    kevinr33 is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    2
    I posted this problem from my home pc, whereas I'm working on this from office. The table is already set in MS Access, but just for convenience purposes, I created a dummy table in Excel and attached it here so everyone can see the structure of the table, that's all. So I already have a normalized table structure.
    My main problem now is how to go about automating the printing of the graphs for each country as I don't want to sit and manually keep clicking print for each one of those 100 countries!

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You can actually create a UNION query that pulls data from your country table & union that with the word ALL and use that to populate the country combo box. From there, you can use the After Update event of the year combo box to do the graphs. Doing an individual graph in Access is usually done via another form which is not too difficult from what I have read, but I have never done one myself. I'm not sure whether doing 100 within Access will cause any problems. It would basically need to create 100 new forms each time! It would probably be better to use Visual Basic for Application to dump each country's data from Access and plot each on separate worksheet plies within an Excel workbook. That way you don't clutter up Access with a bunch of forms.

    The code in the After Update event of the combo box would check to see if ALL was selected or a specific country . If ALL was selected then you would execute a loop that goes through each country and creates the plot in Excel.

    Now, regarding the year, if you always want to have a series for each year, you would need a nested loop within the country loop.

    Do you want your users to have the ability to select specific years? I assume that you will eventually go beyond just 2 years worth of data. Do you want to always plot 2 years (two most recent for which you have data) or the ability to plot many? Do you want to limit the number of years to plot? Those are some other things you will have to consider because they will impact the code you need to write.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adjusting the axis of a graph using VBA
    By Sniper-BoOyA- in forum Programming
    Replies: 0
    Last Post: 08-04-2010, 06:41 AM
  2. Need Zero Values in Table For Graph
    By TexMax007 in forum Access
    Replies: 2
    Last Post: 06-02-2010, 10:02 AM
  3. Form with Subform and Graph
    By DianeG in forum Forms
    Replies: 0
    Last Post: 03-22-2010, 01:47 PM
  4. Pivot table graph form export to powerpoint
    By maati1980 in forum Forms
    Replies: 0
    Last Post: 10-22-2009, 02:59 AM
  5. Chart/Graph
    By Tony McGuire in forum Access
    Replies: 0
    Last Post: 09-13-2009, 04:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums