[AccessD] Access Chart Control - Valid data sources

[John Bodin]

Borge, I must have missed the link to the article you have been referring to.  Can you provide again?  Thank you.






John Bodin

sBOR Office Systems

jbodin at sbor.com<mailto:jbodin at sbor.com>




________________________________
From: AccessD <accessd-bounces at databaseadvisors.com> on behalf of Borge Hansen <pcs.accessd at gmail.com>
Sent: Thursday, October 25, 2018 10:27 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Chart Control - Valid data sources

I found what the problem was.
So just for the record:

The article points out among other things when covering how the chart
control by default transforms the entered rowsource into an aggregated
query:
"By default, each selected field is aggregated. For numeric and currency
fields, the default aggregation is *Sum*. For all other fields, the default
aggregation is *Count*. To change the aggregation, click the down-arrow,
and select from the list, including *None* to remove the aggregation.

*Note*    Text fields must use the *Count* aggregation. All selected *Values
(Y axis) * fields must either be aggregated or nonaggregated."
The simple query I used as rowsource was based on a local table - in turn a
result of a 'make table' query based on an aggregate query.

Realising that the second column in the rowsource was text (Red Face) was
key to solving the problem.

I had selected to format the second column in the make table query as
format(x,'Percent") resulting in a column showing values as xx.xx% , a
string expression carried thru in the make table as a text column.... and
referring to the article ".... Text fields *must* use the *Count*
aggregation."

Removing the format(expression,"Percent") solved my problem...  Ooops!

/borge

PS : Does any one receive the emails I have sent today to AccessD ?
This is the fourth under the same heading.
I haven't so far gotten any copy back into my inbox....
Perhaps my emails don't go thru?
I receive all postings from everyone else - or so it appears
??








On Thu, Oct 25, 2018 at 9:03 PM Borge Hansen <pcs.accessd at gmail.com> wrote:

> This article gives an in-depth presentation of the chart control for
> Access 2016 including how the control transforms the entered rowsource and
> ways to manipulate the transformation including voiding any transformation.
> /borge
>
>
> On Thu, 25 Oct 2018 at 6:52 pm, Borge Hansen <pcs.accessd at gmail.com>
> wrote:
>
>> Hi All,
>> I need a bit of advice as to what makes a valid data source for a Chart
>> Control.
>>
>> Haven't worked with the chart control for many years ..
>> Now using Access 2016 I want to set up a simple line chart.
>> I have a query with two columns: EventYear,
>> NumberOfFamiliesWithChildren_percent.
>> I thought this would be easy to set up with EventYear as the X and the
>> other as Y with values between 0 and 100.
>> When I enter the query as the rowsource on the chart control - set up as
>> a simple line chart -  the wizard (or whatever) creates what is called a
>> transformed rowsource which is the entered query grouped on EventYear and
>> the other column expressed as either sum() or count() - or ... which is not
>> what I want ... I just want the chart control to use the rowsource I have
>> specified without doing any transformation on the rowsource.
>>
>> I've googled and nothing so far has addressed my issue.
>>
>> So, anyone who can give me a pointer to what am I missing here?
>> Why does the control appear to have to perform a transformation on the
>> entered rowsource doing a grouping, sum(), count() etc on the entered
>> rowsource columns?
>>
>> /borge
>>
>>
>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com