Chart transformed row source reverses grouping order

[JamesDoingAccessStuf]

Hey everyone,

I've been building this database for a local library reading program. I'm now trying to generate charts to show various reading visits throughout the day.

I created the query below to display the data in a way that makes sense, that is grouped by day and then time:

SELECT TblStudentVisitsToLibrary.[DayOfReadingVisit], TblStudentVisitsToLibrary.[TimeSlot], Count(TblStudentVisitsToLibrary.[VisitID]) AS CountOfVisitID
FROM TblStudentVisitsToLibrary
GROUP BY TblStudentVisitsToLibrary.[TimeSlot], TblStudentVisitsToLibrary.[DayOfReadingVisit]
ORDER BY TblStudentVisitsToLibrary.[DayOfReadingVisit], TblStudentVisitsToLibrary.[TimeSlot];

MS access then takes my query and reverses the grouping making the data unreadable. This is what it ends up showing (From the Transformed Row Source):

SELECT qryPopularityOfReadingVisitsByDay.[DayOfReadingVisit], qryPopularityOfReadingVisitsByDay.[TimeSlot], Sum(qryPopularityOfReadingVisitsByDay.[CountOfVisitID]) AS SumOfCountOfVisitID
FROM qryPopularityOfReadingVisitsByDay
GROUP BY qryPopularityOfReadingVisitsByDay.[DayOfReadingVisit], qryPopularityOfReadingVisitsByDay.[TimeSlot]
ORDER BY qryPopularityOfReadingVisitsByDay.[TimeSlot], qryPopularityOfReadingVisitsByDay.[DayOfReadingVisit];

I can't seem to edit the transformed row source directly without getting the following error: "You can't assign a value to this object"

I don't feel very familiar with how charts work in access so sorry if I'm missing something obvious. I tried googling it but google didn't seem to understand me.

Any solution that would allow me to control the way the data is grouped on the chart would be amazing.

Thanks so much, James

 

[isladogs]

Which version of Access are you using and are you using traditional or modern charts?
See my responses in this current thread for info on chart typeshttps://www.access-programmers.co.uk/forums/showthread.php?t=306549

Welcome to AWF as well

 

[JamesDoingAccessStuf]

Thanks isladogs, it seems I'm using the modern charts as I believe I used the insert chart dropdown menu

 

[isladogs]

I'm on my tablet which has Access 2010 so I can't access those charts now.
In your chart design view, go to the data tab of the chart property sheet. Try pasting the sql from your query directly into the chart row source in place of what it already has. You might want to make a copy of the chart to do this

 

[JamesDoingAccessStuf]

Interesting. When I paste the following into the row source:

SELECT TblStudentVisitsToLibrary.[DayOfReadingVisit], TblStudentVisitsToLibrary.[TimeSlot], Count(TblStudentVisitsToLibrary.[VisitID]) AS CountOfVisitID
FROM TblStudentVisitsToLibrary
GROUP BY TblStudentVisitsToLibrary.[TimeSlot], TblStudentVisitsToLibrary.[DayOfReadingVisit]
ORDER BY TblStudentVisitsToLibrary.[DayOfReadingVisit],  TblStudentVisitsToLibrary.[TimeSlot];

It then reverses the grouping to below
Access changes the row source I just pasted to:

SELECT TblStudentVisitsToLibrary.DayOfReadingVisit, TblStudentVisitsToLibrary.TimeSlot, Count(TblStudentVisitsToLibrary.VisitID) AS CountOfVisitID
FROM TblStudentVisitsToLibrary
GROUP BY TblStudentVisitsToLibrary.DayOfReadingVisit, TblStudentVisitsToLibrary.TimeSlot
ORDER BY TblStudentVisitsToLibrary.DayOfReadingVisit, TblStudentVisitsToLibrary.TimeSlot;

The chart changes it to the Transformed Row Source of:

SELECT [%$##@_Alias].[DayOfReadingVisit], [%$##@_Alias].[TimeSlot], Sum([%$##@_Alias].[CountOfVisitID]) AS SumOfCountOfVisitID
FROM (SELECT TblStudentVisitsToLibrary.[DayOfReadingVisit], TblStudentVisitsToLibrary.[TimeSlot], Count(TblStudentVisitsToLibrary.[VisitID]) AS CountOfVisitID
FROM TblStudentVisitsToLibrary
GROUP BY TblStudentVisitsToLibrary.[TimeSlot], TblStudentVisitsToLibrary.[DayOfReadingVisit]
ORDER BY TblStudentVisitsToLibrary.[DayOfReadingVisit],  TblStudentVisitsToLibrary.[TimeSlot])  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].[DayOfReadingVisit], [%$##@_Alias].[TimeSlot]
ORDER BY [%$##@_Alias].[TimeSlot], [%$##@_Alias].[DayOfReadingVisit];

Why is access modifying my query by swapping the GROUP BY order?

 

[JamesDoingAccessStuf]

Can I override this and make it group the way I want it to?

Thanks again

 

[isladogs]

That look very wrong!
Can you upload a cut down version of your database and I'll look at it tomorrow when I'm at my computer.

Or in the meantime try using the traditional charts...

 

[JamesDoingAccessStuf]

Hey again isladogs,

I appreciate your offer of help.

I found a work around by using VBA record set to merge both data sets together (day and time). The outcome is a less visually appealing chart but it works. I don't feel I have the time or desire to try and further try and figure out what happened here at this point.

Thanks checking it out this far

 

[isladogs]

OK. Glad you now have something you're happy with