Chart in Report based off Query not working

[matthewh86]

Hi all,

Sorry for the long post! I'm learning MS Access as I'm attempting to get an excel spreadsheet converted into something more fit for purpose. I'm wanting to store information on development stories, so that I can refer to past development, and eventually plot velocity.

The tables I have at the moment are:

• tbl_Stories - the main data table
• tbl_Feature - most stories are linked to an overarching feature, which groups stories together
• tbl_ClassOfWork - contains what kind of work a story is (e.g. Functional, Support Bug, Architectural)
• tbl_Size - contains the sizes of stories (e.g. Small, Medium, Large) and their point value (e.g. 1, 2, 4, 8)
• tbl_Status - contains the various statuses a story can be (e.g. Planned, Complete, Rejected)
• tbl_CycleSprint - contains the date ranges for each cycle and sprint (e.g. cycle 30 sprint 1 is has a start date of 30/01/2017 and end date of 03/02/2017)

I have a query that joins the tables together so that all the information is available, another that omits some of the columns returned, and another that filters based on the story being "Complete".

I have created a report based on this last query, and grouped by End_Cycle_No, and Cycle_Sprint, which works, but when I try and follow this tutorial

• youtube video OMrHN1tUb8U (sorry, can't post link due to forum rules)

it doesn't work when I try and base it off the view_Story_Complete Query

EDIT: I have attached a relationship diagram of the tables and views in the post below.
EDIT: I have also attached the database itself.

This is the SQL that generates the joining view:

SELECT tbl_Story.*,
tbl_Feature.FT_Number,
tbl_Feature.Description,
raisedCycleSprint.Cycle_No AS Raised_Cycle_No,
"CD" & raisedCycleSprint.Cycle_No & "-" & raisedCycleSprint.Sprint_No AS Raised_Cycle_Sprint,
startCycleSprint.Cycle_No AS Start_Cycle_No,
"CD" & startCycleSprint.Cycle_No & "-" & startCycleSprint.Sprint_No AS Start_Cycle_Sprint,
endCycleSprint.Cycle_No AS End_Cycle_No,
"CD" & endCycleSprint.Cycle_No & "-" & endCycleSprint.Sprint_No AS End_Cycle_Sprint,
tbl_ClassOfWork.Description AS Class_of_Work_Desc,
tbl_Size.Size AS Story_Size_Desc,
tbl_Size.Story_Points AS Story_Points,
tbl_Status.Description AS Status_Desc
FROM ((((((tbl_Story
LEFT JOIN tbl_Feature ON tbl_Story.[FT_ID] = tbl_Feature.ID)
LEFT JOIN tbl_CycleSprint AS raisedCycleSprint ON (tbl_Story.Raised_Date >=raisedCycleSprint.Sprint_Start) AND (tbl_Story.Raised_Date <= raisedCycleSprint.Sprint_End))
LEFT JOIN tbl_CycleSprint AS startCycleSprint ON (tbl_Story.Start_Date >=startCycleSprint.Sprint_Start) AND (tbl_Story.Start_Date <= startCycleSprint.Sprint_End))
LEFT JOIN tbl_CycleSprint AS endCycleSprint ON (tbl_Story.Start_Date >=endCycleSprint.Sprint_Start) AND (tbl_Story.Start_Date <= endCycleSprint.Sprint_End))
LEFT JOIN tbl_ClassOfWork ON tbl_Story.Class_of_Work = tbl_ClassOfWork.ID)
LEFT JOIN tbl_Size ON tbl_Story.Story_Size = tbl_Size.ID)
LEFT JOIN tbl_Status ON tbl_Story.Status = tbl_Status.ID
ORDER BY tbl_Story.ID;

The column omission view:

SELECT sql_StoryJoins.ID,
sql_StoryJoins.FT_Number,
sql_StoryJoins.tbl_Feature.Description,
sql_StoryJoins.User_Story_ID,
sql_StoryJoins.Task_No,
sql_StoryJoins.Bugzilla_ID,
sql_StoryJoins.QSM_ID,
sql_StoryJoins.ServiceNow_ID,
sql_StoryJoins.tbl_Story.Description,
sql_StoryJoins.Class_of_Work,
sql_StoryJoins.Class_of_Work_Desc,
sql_StoryJoins.Story_Size_Desc,
sql_StoryJoins.Story_Points,
sql_StoryJoins.Status_Desc,
sql_StoryJoins.SRA_Required,
sql_StoryJoins.Planned,
sql_StoryJoins.Raised_Date,
sql_StoryJoins.Raised_Cycle_No,
sql_StoryJoins.Raised_Cycle_Sprint,
sql_StoryJoins.Start_Date,
sql_StoryJoins.Start_Cycle_No,
sql_StoryJoins.Start_Cycle_Sprint,
sql_StoryJoins.End_Date,
sql_StoryJoins.End_Cycle_No,
sql_StoryJoins.End_Cycle_Sprint,
sql_StoryJoins.Comments
FROM sql_StoryJoins;

And the last filtering view:

SELECT
view_Story.ID,
Switch(
	view_Story.[FT_Number] Is Not Null,"FT-" & view_Story.[FT_Number] & " - " & [tbl_Feature].[Description],
	view_Story.Class_of_Work_Desc="Project Bug","Misc Project Bugs",
	view_Story.Class_of_Work_Desc="Support Bug","Support Bugs",
	True,"Misc") AS Work_Details,
Switch(
	view_Story.Task_No Is Not Null,"US-" & view_Story.User_Story_ID & "-" & view_Story.Task_No,
	view_Story.User_Story_ID Is Not Null,"US-" & view_Story.User_Story_ID,
	True,Null) AS User_Story_Task,
Switch(
	view_Story.Bugzilla_ID Is Not Null,"BZ" & view_Story.Bugzilla_ID,
	view_Story.QSM_ID Is Not Null,"QSM" & view_Story.QSM_ID,
	view_Story.ServiceNow_ID Is Not Null,view_Story.ServiceNow_ID,
	True,Null) AS Bug_ID,
view_Story.tbl_Story.Description,
view_Story.Class_of_Work,
view_Story.Class_of_Work_Desc,
view_Story.Story_Size_Desc,
view_Story.Story_Points,
view_Story.SRA_Required,
view_Story.Planned,
view_Story.Raised_Date,
view_Story.Raised_Cycle_No,
view_Story.Raised_Cycle_Sprint,
view_Story.Start_Date,
view_Story.Start_Cycle_No,
view_Story.Start_Cycle_Sprint,
view_Story.End_Date,
view_Story.End_Cycle_No,
view_Story.End_Cycle_Sprint,
view_Story.Comments
FROM view_Story
WHERE (
(view_Story.Status_Desc)="Complete");

The table I want for each of the cycles, plots Cycle/Sprint against Points, with Class of Work as each series, represented as a stacked bar graph. So I select those fields, but when I select the End_Cycle_No to link the report and chart fields, when previewing I get the error:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Followed by

"An error occurred while sending data to the OLE server (the application used to create the object)."

Could someone suggest how I can get this working? Thanks for reading this far

 

[matthewh86]

Added a zip, as I am allowed to upload a zip.

 

[JHB]

Post the database also.

 

[matthewh86]

Attached database.

 

[JHB]

I've got the graph to show data, but I'm nearly sure it isn't total correct, so could you show some print screen, the way you want it.
The fault you've done was not linking the report and the graph with the correct "Master Link Fields" and "Child Link Fields".

 

[matthewh86]

It's almost there, I want to display the complete cycle which has multiple sprints so that they can be compared with each other.

Ideally I'd want to be able to configure a view so that multiple cycles and sprints can be compared with each other, as in the attachment.

 

[JHB]

Open the report "Multiple cycles".
If you want the average, then show it using the secondary axes.

 

[matthewh86]

Thanks! Is there a way to embed this into the report which has the list of stories, grouped by End_Cycle_No?

 

[JHB]

Could you show (picture) of what exactly you want, print screen or something else?

 

[matthewh86]

Sure, sorry I'm not clear. I've attached a mockup.

The report is grouped as follows:

End_Cycle_No (e.g. Cycle 17)
-graph of each sprint in one graph
Sprint_Cycle_End (e.g. CD17-1, CD17-2, CD17-3)
-details of the stories

 

[JHB]

Open report "rpt_CompletedStories".

 

[matthewh86]

Great! Thanks very much .

I have another query, should I make a new post or continue here?

 

[JHB]

If it has nothing to do with the problem you have in here, then make a new thread.