graph / chart from query without summarising data

[thecheyenne]

Dear All!
I have a query that holds test results for my students; each test is either a listening-test, reading-test, speaking-test or writing-test. All results are in % and already calculated by the query using the expression
Percentage: Results!Score*100/Assignments![out of]

I'd like to convert these results into a simple [:):):)] bar chart and I'd like to add this chart to each student's report card, which is essentially a simple Access-Report.

The query I want to use for the graph has these fields:

Students.StudentID: to link the graph to the student's report card
Subjects&Years.SubjectyearID: to identify the class the student is in; I might teach the same student in two different classes;this field serves as a further link between graph and studentreport card
Assignments.Skill_Area: to identify the type of test, i.e. reading/writing/listening/speaking
AssignmentDistribution.AssignmentDistributionDate: simply holds the date the test was administered
AssignmentDistribution.Type: to identify an assignment as a test, rather than a homework or some other assignment.

Unfortunately, I'm not yet good enough to tell the graph/chart-wizzard that it needn't summarise or format the data in any way, i just want it to show it in a simple bar graph, where

• the value-axis on the left shows the values 0%,25%;50%,75%,100% - this would be the same for each and every graph
• the category -axis at the bottom shows the assignment_distribution_dates, from left to right - this would also be the same for every graph
• and the bars would represent the skill-areas - each graph would have the same number of bars but showing different test-results, using the %-values from the underlying query -

I've searched my trusty O'Reilly - Access Cookbook, my Access-4-Dummies but can't seem to find anything that helps me get this kind of graph done.

Is it possible?

[FishVal]

It would be helpful to see the SQL expression of your query.

Kind regards.
Fish

[thecheyenne]

Sorry, I can see how that might be helpful, so here it is:

SELECT Results!Score*100/Assignments![out of] AS Percentage, Results.StudentID, Assignments.[Skill-Area], [Assignment-Distribution].SubjectYearID, Results.AssignmentDistributionDate
FROM Assignments INNER JOIN ([Assignment-Distribution] INNER JOIN Results ON [Assignment-Distribution].AssignmentID = Results.AssignmentID) ON Assignments.AssignmentID = [Assignment-Distribution].AssignmentID
WHERE ((([Assignment-Distribution].SubjectYearID)=14) AND (([Assignment-Distribution].Type)="End-of-Unit-Test"))
ORDER BY Results.AssignmentDistributionDate;

The WHERE-clause is here because, this being a pilot-scheme, a learning-experience, for now I only want the data for one class, the class with the subjectYearID 14.

All assignments are stored in a table - tbl_assignemnts, in which assignmentID is the primary key.

tbl_assignments also holds information as to what the maximum score for each assignment might be; this is stored in the field 'out_of'.

But an assignment really only becomes 'active' - so to speak - once it has been paired up with a class (subject-year-ID)and given an "AssignmentDistributionDate" in the table "AssignmentDistribution".

A separate query then combines the AssignmentID from tbl_AssignmentDistribution with all StudentIDs for the relevant class and dumps the lot into tbl_results.

Then, when the assignment has been marked, a numerical result is stored in the field 'score' in the tbl_results.
This score is then taken to calculate a percentage.

Have I forgotten anything??

Thanks for reading

[FishVal]

Well. It is still somewhat unclear to me.
Could you please post an example of data stored in those three tables and a resulting dataset returned by the query?

[thecheyenne]

O.k, here it goes, I hope this format is acceptable, I wouldn't know how else to describe it. I do appologise for any database design flaws and am grateful for any improvement tips.

The following tables are linked (1-to-many) via their individual -ID fields; these fields are also primary key fields

tbl.assignments (holds all info relating to assignments, in other words, the tests that my graph should display
AssignmentID - 1
Assignment - Readingtest on Eiffel Tower
Out_of - 10
Skill-Area - Reading (there are 4 skill areas (read/writ/list/speak) and they are all stored in a look-up field

tbl.SubjectYears is a 'junction-table' to pair up the subjects I teach and the age-groups I teach. Each SubjectYearID represents a single class - without students in it.
SubjectYearID - 1
SubjectID - 1 (French)
YearGroupID - 2 (5th grade)

tbl.teachingGroups - is the 'container' for classes with students in it
TeachingGroupID - 1
SubjectYearGroupID - 1 (French/5th grade)
StudentID - 1 (Freddy French)

tbl.AssignmentDistribution
AssignmentID - 1 (Reading Test on Eiffel Tower)
SubjectYearID - 1 (French / 5th Grade)
AssignmentDistributionDate - 23/06/2010
AssignmentType - Test (there are other types - homework; classwork etc, but not enough types to warrant a separate table)

tbl.Results the first 3 fields are 'shared primary key fields - so that Freddy can do the Reading test again on another date, if he fails; unfortunately, in today's world, I have to also keep a record of how many goes he has before he actually passes the test.
AssignmentID - 1 (Reading Test on Eiffel Tower)
StudentID - 1 (Freddy French)
AssignmentDistributionDate - 23/06/2010
Score - 8
================================================== ===
Now for the Query - qryFrenchModuleTests
The SQL-statment

SELECT Results!Score*100/Assignments![out of] AS Percentage, Results.StudentID, Assignments.[Skill-Area], [Assignment-Distribution].SubjectYearID, Results.AssignmentDistributionDate
FROM Assignments INNER JOIN ([Assignment-Distribution] INNER JOIN Results ON [Assignment-Distribution].AssignmentID = Results.AssignmentID) ON Assignments.AssignmentID = [Assignment-Distribution].AssignmentID
WHERE ((([Assignment-Distribution].SubjectYearID)=14) AND (([Assignment-Distribution].Type)="End-of-Unit-Test"))
ORDER BY Results.AssignmentDistributionDate;

returns data looking like this:


Skill-Area / % / StudentID / SubjYearID / Date
Reading / 93.33 / 375 / 14 / 16-Nov-09
Writing / 91.67 / 375 / 14 / 16-Nov-09
Speaking / 76.67 / 375 / 14 / 16-Nov-09
Listening / 86.67 / 375 / 14 / 16-Nov-09
Reading / 80.00 / 376 / 14 / 16-Nov-09

This query sorts all tests according to Student, so Freddy French (StudentID 375)has his 4 tests listed before the same 4 tests are listed for StudentID 376.

Does this help?

I would now like to learn how to display this data in simple bar-chart from but somehow can't get it done.