Creating Charts in Access 1

[davidchipman]

I have created a line chart so I can track billings over the past 3 years. The chart is not working the way that I want it to though. The problem is that it shows the billings for the last 3 years. Instead of overlapping (i.e the month of February displaying feb 00, feb 01 and feb 02) the bottom axis shows Jan 01, Feb 01, ....Dec 02 and plots it that way so there is no overlapping. I want it to just show months so that when I look at the chart I can visually see the different years over the specific month I am looking for (i.e Did we make more money in December 2002 than we did in 2001). I need it to overlap and on the bottom axis just to display the months and not the months and years. any help would be greatly appreciated...thanks

 

[shannonp]

Could you post a small sample of how your data is arranged?

 

[davidchipman]

Sample of what it is I am trying to do...I will use February as an example

In february 2001, I have 62 new clients worth an estimated billings of $86,000.

In February 2002, I have 73 new clients worth an estimated billings of $89,500.

When a new client is entered into the database, I enter the date in the required field and it comes up 2/24/02.

So in my line graph, I want my axes to just say february so that I can plot my line graphs over each other to see how business is growing...now, when I use the wizard, on the bottom axes it plots every month in order as they occur (i.e Jan 02, Feb 02, Mar 02......Jan 03). If yo ucould help it would be greatly appreciated. thanks

 

[Maquis]

David,

I'm just learning to construct graphs myself, so this advice may or may not help you. I came across this thread while looking for some help myself.

I believe you can accomplish what you want by adding your date field to the series box in the Chart Wizard. Double click the field after it's in the box and a grouping dialog box will pop up where you can choose to show series by year.

Good Luck! Maq
<insert witty signature here>

 

[davidchipman]

thanks Maq
I had tried that to but it still puts up the month and the year and does not isolate it to just February March april May June July Aug...it keeps it as feb 02, mar 02...so it does not plot over each other...any other help would be greatly appreciated
dave

 

[shannonp]

So if I'm right in assuming you essentially have 2 variables:

Variable 1: [Date]
Variable 2: [Billing Amount]
* taken that these variables can be grouped and calculated within a query.

My advice would be (once you've summised the above vairables) to use a crosstab query:

Make Row Heading = Format([Date],&quot;mmm&quot <Grouped>
Make Column Heading = Format([Date],&quot;yyyy&quot <Grouped>
Make Value = [Billing Amount] <Sum>

Once you have designed your crosstab query, then make a Report -> choose Chart Wizard -> select all available fields -> choose your desired graph type (presume line or bar) -> then just choose finish...nearly done. You'll notice only the first series is visible, so go to the properties window of the graph -> Open Row Source (which takes the form of a query) and add more series (in this case YEARS), making sure that they are summed and not grouped.

View your Report and hopefully you'll have the desired output...if you have any problems I can send you a small sample report with underlying tables if you like.

 

[pdldavis]

Hi, I don't know that this will help you out, but the following sets the X-Axis min and max values. The values are loaded from a form. You have to have the chart library loaded for this to work:

Private Sub YourGraph_Updated(Code As Integer)
On Error GoTo Err_YourGraph_updated
Dim C As Chart
Dim X As Axis
Set C = Me!YourGraph.Object
Set X = C.Axes(xlCategory)
With C.Axes(xlCategory)
.MinimumScale = Forms!frmPQ.CStartDate
.MaximumScale = Forms!frmPQ.CEndDate
End With
DoCmd.Requery
exit_YourGraph_UPDATED:
Exit Sub
Err_YourGraph_updated:
MsgBox &quot;This Graph Contains no Data. Closing Form.&quot;
DoCmd.Close acForm, &quot;FrmProj&quot;, acSaveNo
DoCmd.OpenForm &quot;frmPQ&quot;, acNormal

Resume exit_YourGraph_UPDATED
End Sub

 

[davidchipman]

Hi Shannonp1
I am so close to getting it working but I am missing something...If you could send me the small report it would be greatly appreciated..thanks kindly
dave

 

[davidchipman]

Hi All
I got it working...thanks for everyones help.
dave

 

[shannonp]

Great to hear you solved it , sorry about not replying earlier...but I just got off the weekend