Solved DYNAMIC REPORT FROM CROSSTAB QUERY

[I am alive]

check again if the "mean score" is correct.

Thanks so much. It works perfectly. Is there any way to hide those labels for subjects at the report footer?

 

[arnelgp]

goto Sub set_controls(), and comment this line:

..
..
        'With Me.Controls("X" & Format$(i + 1, "00"))
        '    .Caption = lbl(i)              'DLookup("Description", "Subjects", "[Subject ID] = '" & lbl(i) & "'") & ""
        '    .Visible = True
        'End With
..
..

 

[I am alive]

goto Sub set_controls(), and comment this line:

..
..
        'With Me.Controls("X" & Format$(i + 1, "00"))
        '    .Caption = lbl(i)              'DLookup("Description", "Subjects", "[Subject ID] = '" & lbl(i) & "'") & ""
        '    .Visible = True
        'End With
..
..

When I input above code I get the following error
Run-time error '2465'
Microsoft Access can't find the field 'X01' referred to in your expression.

 

[arnelgp]

here i already Hide it.

 

[I am alive]

Please kindly check at this database. Kindly look at qrySubjectStudentCount. Please check MEANGRADE and POINTS. Kindly check at the report. I have updated controls and subcontrols for subjects. When I run the report I get the following error

'The Microsoft Access database engine does not recognize '[false]' as a valid field name or expression.

The controls for MEANGRADE IS "M"
and for POINTS is "P".
Kindly assist me.

 

[arnelgp]

see this again.

 

[I am alive]

see this again.

In this attached database i have created a form print examination reports. Kindly assist me on how to filter based on grades and only show meanscore for subjects entered and hide those not entered

 

[I am alive]

When I try to filter reports it shows meanscore for all subjects. How do you hide the meanscore for subjects not entered

 

[I am alive]

I created another table for GRADES - FORM 1, FORM 2, FORM 3 and FORM 4 and another one for STUDENTSGRADES and I have created a relationship. I have used @arnelgp report and it works perfectly. I am only defeated in how to filter the report by form so that when you select GRADE e.g FORM 1 I should be able to see reports for FORN ONE STUDENTS ONLY. Anybody with any idea will be highly appreciated.

 

[arnelgp]

check the code on the button on your form.

 

[I am alive]

check the code on the button on your form.

check the code on the button on your form.

This one doesn't filter to show the meanscore for subjects done by the Students. For example if you select the grade FORM 2, the subjects which were not done by Students still you could see the Meanscores. You can still see the Meanscores from the main report i.e AGRI, ARAB, ART, AVIA, COMP, CRE, GEO, GERM, HRE, HSC IRE, POWER and WOOD. I need this fields to be hidden and show the meanscore for subjects done only.
(SUBJECTS ARE NULL AND MEANSCORE HAS VALUES AFTER BEING FILTERED. HIDE NULL SUBJECTS AND SHOW ONLY MEANSCORE FOR SUBJECTS SHOWN)
Please confirm. Thanks in advance

 

[I am alive]

Kindly assist me with this. I have tried everywhere but the meanscore, mean grade and points are still visible after filtering. This is the last help. Please.

 

[arnelgp]

check this filtered report.

 

[I am alive]

check this filtered report.

Thanks so much. I really appreciate what you do. I know it is tedious. However with this filtered report, there is a slight problem. The Meanscores are being brought the way it is from the main report. The Meanscores being filtered should be able to fit the filtered report. E.g when you filter FORM 2, BIO the subject was done by only one child who scored 50 meaning the meanscore should be 50/1=50 and not 47.6. same thing with CHEM only one child scored 50 and the meanscore should be 50/1=50 and not 58.4. I really really appreciate what you do. I wish there was a way of appreciating you even more. I don't take for granted.

 

[I am alive]

Thanks so much. I really appreciate what you do. I know it is tedious. However with this filtered report, there is a slight problem. The Meanscores are being brought the way it is from the main report. The Meanscores being filtered should be able to fit the filtered report. E.g when you filter FORM 2, BIO the subject was done by only one child who scored 50 meaning the meanscore should be 50/1=50 and not 47.6. same thing with CHEM only one child scored 50 and the meanscore should be 50/1=50 and not 58.4. I really really appreciate what you do. I wish there was a way of appreciating you even more. I don't take for granted.

I have learned alot from you @arnelgp

 

[I am alive]

Thanks so much. I really appreciate what you do. I know it is tedious. However with this filtered report, there is a slight problem. The Meanscores are being brought the way it is from the main report. The Meanscores being filtered should be able to fit the filtered report. E.g when you filter FORM 2, BIO the subject was done by only one child who scored 50 meaning the meanscore should be 50/1=50 and not 47.6. same thing with CHEM only one child scored 50 and the meanscore should be 50/1=50 and not 58.4. I really really appreciate what you do. I wish there was a way of appreciating you even more. I don't take for granted.

The filtered report should be independent and not inherit from the main report. I really appreciate your help.

 

[arnelgp]

i thought Mean score is the Total Scores divided by the Number of Students taking that Subject?
the current calculation is doing that, it's not dependent of the Grade.

if there are 3 student taking English (2 for forum 2 and 1 on Forum 3), then the mean score
is the cummultive scores divided by 3?

here is a new version.
i also delete 2 records which have same student/subject with different grades from table StudentSubject.

 

[I am alive]

i thought Mean score is the Total Scores divided by the Number of Students taking that Subject?
the current calculation is doing that, it's not dependent of the Grade.

if there are 3 student taking English (2 for forum 2 and 1 on Forum 3), then the mean score
is the cummultive scores divided by 3?

here is a new version.
i also delete 2 records which have same student/subject with different grades from table StudentSubject.

Perfect. It works. Thanks

 

[I am alive]

Hi again. I thought after finding the MEANGRADE and POINTS for all SUBJECTS DONE and selected by Students, it was going to be easy to find the CLASSMEANGRADE and CLASSPOINTS BY ADDING SUBJECT MEANSCORES AND USING IIF. But it has given me headache for the whole day and night. Kindly check again this attached database and assist me to get CLASSMEANGRADE AND CLASSPOINTS which should be filtered in filter by form. I tried using this query:

SELECT Sum(qrySubjectStudentCount.Meanscore) As SumOfMeanScore, Count(qrySubjectStudentCount.MeanScore) As CountOfMeanScore, Sum([qrySubjectStudentCount].[Meanscore])/Count([qrySubjectStudentCount]. Meanscore As MEAN

AFTER THAT I used IIF([MEAN])>=80,"A", and so on.

After i joined this query with qryGrades and it works perfectly.
However, it cannot be filtered.
Check this database. I put a label in MEANGRADE and POINTS.
I am really grateful for your assistance. I believe this is the last hurdle.
Thanks in advance.

 

[I am alive]

Hi again. I thought after finding the MEANGRADE and POINTS for all SUBJECTS DONE and selected by Students, it was going to be easy to find the CLASSMEANGRADE and CLASSPOINTS BY ADDING SUBJECT MEANSCORES AND USING IIF. But it has given me headache for the whole day and night. Kindly check again this attached database and assist me to get CLASSMEANGRADE AND CLASSPOINTS which should be filtered in filter by form. I tried using this query:

SELECT Sum(qrySubjectStudentCount.Meanscore) As SumOfMeanScore, Count(qrySubjectStudentCount.MeanScore) As CountOfMeanScore, Sum([qrySubjectStudentCount].[Meanscore])/Count([qrySubjectStudentCount]. Meanscore As MEAN

AFTER THAT I used IIF([MEAN])>=80,"A", and so on.

After i joined this query with qryGrades and it works perfectly.
However, it cannot be filtered.
Check this database. I put a label in MEANGRADE and POINTS.
I am really grateful for your assistance. I believe this is the last hurdle.
Thanks in advance.

Kindly please assist me.