Find different grades on a report

[Ayiramala]

Hi,
I have a certain problem and will much appreciate it if anyone can help me with it:


I have a form (and report) which show the grades students got in an exam. There are several subjects. So what we see here is: we have the students' names in the first column, followed by columns containing their grades. The columns titles are the names of the subjects. Like this:

Physics Chemistry Math Biology
Alan A B A C
John C A+ B D

I would like to know how we can find those who got A+ in every subject, A in every subject, A+ in five subjects, those who lost A+ in one subject etc. How can we achieve this?
Any help is truly appreciated. Thanks.

[orange]

What do you mean by Find...on a Report??

You can query values in your tables, and use that query as a recordsource for a report.

But your post suggests using a query. It has nothing to do with a report.

[June7]

Your data structure is not normalized. What you want will not be simple. Example expression to determine how many subjects are A+:

IIf(Physics="A+",1,0) + IIf(Chemistry="A+",1,0) + IIf(Math="A+",1,0) + IIf(Biology="A+",1,0)

What 5 subjects - you show only 4.

[Ayiramala]

I should have said query, not report. Sorry.
There are not five but six subjects. I showed only four as an example, thinking I could work out the remaining two.
My data structure is not normalized... Could you suggest how I could modify it?

[June7]

Consider:

StudentID	SubjectID	Grade
1	1	A+
1	2	B
1	3	A
1	4	C
1	5	B+
1	6	C-

SELECT StudentID, Grade, Count(*) AS CountGrades FROM tblGrades GROUP BY StudentID, Grade;

A UNION query could rearrange your current data into this structure.

[Ayiramala]

Consider:

StudentID	SubjectID	Grade
1	1	A+
1	2	B
1	3	A
1	4	C
1	5	B+
1	6	C-

SELECT StudentID, Grade, Count(*) AS CountGrades FROM tblGrades GROUP BY StudentID, Grade;

A UNION query could rearrange your current data into this structure.

I'll do as directed. Thanks a lot.

[Ayiramala]

May I ask for a favor?
I attach a db with three tables. Could you take a look at it and create a score-entry form in it? I haven't created any relationships between them. Could you do that too? Let's say that the students belong to different divisions.
Thanks.

[orange]

Let's say that the students belong to different divisions.

You should provide a description in plain English of your problem/issue/opportunity. Readers need some context for things such as the quote I highlighted.
How does a Division relate to a Student? Readers are not clairvoyant.

I recommend you work through this tutorial from Rogers Access Library. You will learn concepts for creating tables and relationships, normalization leading to a database design to meet a described business requirement. An hour or two with this will help you tremendously.

Good luck.

[June7]

No, not going to build for you. I agree with orange, you need to work through some tutorials and get a better understanding of relational database principles, Access functionality, programming concepts, programming with macros and/or VBA.

The db you posted doesn't even have a table for recording student grades. This table would look like the example I suggested in post 5.

If you want to associate a student with division then have a field in tblStudents for that data.