Question Chart from 2 tables

[Dzib]

Hi,


I'm trying to figure out how to combine two tables within a chart.
I have a table "A" with the fields "eventA" and "dateeventA"
I have a table "B" with the fields "eventB" and "dateeventB"


My goal is to have a line chart with the last 5 years on the horizontal bar of the chart and one line with the count of "eventA" for each year and another line for the count of "eventB" also year by year.


I've tried with queries but I can't find out how to combine the years...

One more question, is it possible to rename (via VBA) the legends of a chart? I need to do it via VBA because my form is multilingual

Any help would be greatly appreciated

 

[isladogs]

Suggest you make an aggregate query for table A and add a field YearEventA: Year(DateEventA). Group by YearEventA and count the EventA field.
Repeat for table B.
Now create a third query based on the first two queries and join using the extra fields created. Add the count and group by fields to that. You can filter to only show data for the last 5 years
Make a suitable bar chart based on the third query.

You should be able to modify the legend using vba but I'm answering on a tablet and can't check the code now.
However the method will be different depending on whether you are using the traditional charts or the new modern charts available in A365

 

[Dzib]

Suggest you make an aggregate query for table A and add a field YearEventA: Year(DateEventA). Group by YearEventA and count the EventA field.
Repeat for table B.
Now create a third query based on the first two queries and join using the extra fields created. Add the count and group by fields to that. You can filter to only show data for the last 5 years
Make a suitable bar chart based on the third query.

Got it!!! Thank you so much!

You should be able to modify the legend using vba but I'm answering on a tablet and can't check the code now.
However the method will be different depending on whether you are using the traditional charts or the new modern charts available in A365

I'm using the modern charts...

 

[isladogs]

I'm using the modern charts...

I rarely use these as the charts aren't visible to users with older versions of Access.
Unfortunately there isn't a lot of info available online other than this article: https://support.office.com/en-us/article/create-a-chart-on-a-form-or-report-1a463106-65d0-4dbb-9d66-4ecb737ea7f7?ns=MSACCESS&version=90&ui=en-US&rs=en-US&ad=US

However a quick test indicated code like this works for changing different items on a chart.

Private Sub Command4_Click()
    Me.Chart0.ChartLegend = "Percent"
    Me.Chart0.LegendPosition = acLegendPositionRight
    Me.Chart0.LegendTextFontColor = vbRed
    Me.Chart0.LegendTextFontSize = 11
    Me.Chart0.PrimaryValuesAxisTitle = "ABC"
    Me.Chart0.ChartTitle = "Bonjour La Monde!"
End Sub

Private Sub Command5_Click()
    Me.Chart0.ChartLegend = "InfoDate"
    Me.Chart0.LegendPosition = acLegendPositionTop
    Me.Chart0.LegendTextFontColor = vbBlue
    Me.Chart0.LegendTextFontSize = 10
    Me.Chart0.PrimaryValuesAxisTitle = "XYZ"
    Me.Chart0.ChartTitle = "Hello World"
End Sub

I was just playing with some of the different items available in the intellisense drop down for the chart. There is a very long list of available items you can alter

See simple test db attached (for Access 365 only)

Although I used buttons I'm sure you could automate this depending on language settings but I have no experience of doing that!

NOTE: The ChartLegend MUST be one of the fields in the record source.
So you may possibly have to duplicate the field used for your data series in the 2 languages you need???

Hope that is of some help

 

[Pat Hartman]

Wouldn't a union query work?

@Dzib,
Hard to tell from what you've posted but you should probably have all the data in a single table and use criteria in your queries to select only the data you want.

 

[Dzib]

Better late than never, thanks a lot for the info, looks like these charts are not very "flexible" as I can't change legend text...

 

[Pat Hartman]

Didn't Colin post code that changes various chart properties? When did the question morph to properties rather than data?

 

[isladogs]

Better late than never, thanks a lot for the info, looks like these charts are not very "flexible" as I can't change legend text...

Yes you can either manually or using code as I showed you in post #4.

You can change almost all modern chart features using code. The only ones I've not been able to change are the colour and position of data values for each graph point (both of which were possible in the older charts).