X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Chart - second axis    
post May 1 2009, 11:56 PM

Posts: 820
Joined: 1-April 09
From: Queensland Australia

under Chart > Options > Titles there are two greyed out areas:
Second Category (X) Axis
Second Category (Y) Axis
Is it possible to have charts with the second X and Y category within MS Access 2007 - in other packages the description of the Y axis would appear on the right hand side.
If so could someone please provide an example
Regards Cec
Go to the top of the page
post May 2 2009, 03:14 PM

Posts: 20,532
Joined: 10-January 04

Ocwac60 (what is your name?)
yes, you can do the same thing in Access. I find it best to manipulate the chart by hand rather than using the wizards -- not even sure if/where the option is
1. double-click on the chart object in the form design view to select it
2. right-click on the data series you wish to show on a secondary axis
3. from the shortcut menu --> Format Data Series
4. Axis tab
5. Plot series on --> Secondary axis
Go to the top of the page
post May 3 2009, 06:27 PM

Posts: 820
Joined: 1-April 09
From: Queensland Australia

Thank you Crystal
Your solution certainly empowers Charting capability.
The next hurdle is to find an easy to edit the text within the legend to have somethng more meaningful than
Ofound that it could be changed by changing the SQL Select Statement via property window from
SELECT ..... AS SumOfColx to SELECT ..... AS somethng_more_meaningful

but am hoping there is a more elegant way. Clicking on Legend object only seems to allow change to font etc
Go to the top of the page
post May 3 2009, 09:59 PM

Posts: 20,532
Joined: 10-January 04

Hi Cec,
I have spent last several hours trying to get info for programmatically manipulating the graph object in Access 2007 -- there is a distinct lack of information! So far, there does not seem to be a NAME property for the series object, which is how I have changed the legend entry in the past.
If your graph is based on a query or directly on SQL, your best bet is to replace the SQL -- not hard. Here is a general procedure you can use if your data comes from a query:
'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
   ByVal pSql As String, _
   ByVal qName As String)

   'modified 3-30-08
   'strive4peace2008 at yahoo dot com

   On Error GoTo Proc_Err

debug.print pSql

   'if query already exists, update the SQL
   'if not, create the query
    If Nz(DLookup("[Name]", "MSysObjects", _
        "[Name]='" & qName _
        & "' And [Type]=5"), "") = "" Then
        CurrentDb.CreateQueryDef qName, pSql
       'if query is open, close it
       on error resume next
       DoCmd.Close acQuery, qName, acSaveNo
       On Error GoTo Proc_Err
       CurrentDb.QueryDefs(qName).sql = pSql
    End If
   Exit Sub
   MsgBox Err.Description, , _
     "ERROR " & Err.Number & "  MakeQuery"
   Resume Proc_Exit
   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
End Sub

to use the MakeQuery procedure, put this in your code:
MakeQuery strSQL, "YourQueryName"
if the SQL is embedded in the chart object RowSource, you will need to use VBA to open the form in design view and replace it -- OR replace it on the OPEN event of report (or form)
to learn more about SQL, read the SQL chapter of this document:
Access Basics
free 100-page tutorial that covers essentials in Access
Go to the top of the page
post May 3 2009, 10:16 PM

Posts: 20,532
Joined: 10-January 04

You can CHANGE SumOfColx to be something better
in SQL, whatever follows AS is the alias of the (calculated) fieldname:
SELECT sum(Colx) AS SumOfColx
if you use spaces or special characters, you need to dlimit with square brackets
SELECT sum(Colx) AS [Sum X]
on the grid, whatever comes before the colon is the alias:
SumOfColx: Sum(Colx)
--> SumX: Sum(Colx)
you can choose anything except the name of another field in the source or a reserved word
Problem names and reserved words in Access, by Allen Browne

if you are not needing to Sum, I would remove it -- the Access chart wizard always does a Group/Total whether it is applicable or not
for the graph object properties, you can change the data for the graph by using the builder button [...] on the RowSource property (once again, Access Basics will have more information on properties)
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    2nd July 2020 - 04:37 AM