Dynamic CrossTab Query (Conditional Sum)

[stinger]

I often use MS Access for small tasks, I am not very good at it, I am already using a small exam system which is not dynamic and I have to change it frequently, I want to create a dynamic cross tab query which I need help with,

I have a query named assessmnetTotals which has a column named GrandTotal and I want to conditionally get the total that is coming in it. It contains the total of all ExamType. I want the total of each exam to be included in the grand total only if the total of this exam type is 50% or more of the total of this class and exam given in the Assessment GT query.

I am attaching the database file.

 

[stinger]

Any assistance from you would greatly simplify things for me, and I would truly appreciate it.

 

[DHookom]

Sorry, I can't get my head around your requirements. Perhaps you could provide some examples based on the data in your file. It's difficult to create a guess without having the target numbers available to check our work.

 

[stinger]

Sorry, I can't get my head around your requirements. Perhaps you could provide some examples based on the data in your file. It's difficult to create a guess without having the target numbers available to check our work.

hope this will make sense, sorry I am not good in English

 

[arnelgp]

i made small queries (query1, query2, query3).
your final query is qryFinal.

 

[stinger]

i made small queries (query1, query2, query3).
your final query is qryFinal.View attachment 117134

Thanks for Your response,
in query3 grandTotal must contain only those marks which are greater than 50% of total marks of that exam, for example if assessment-1 total marks for class 1 are 200 and obtained marks are 99 then it should not include in GrandTotal

 

[arnelgp]

i am doing it already in Query2. marks obtained per class and per subject (see the expression on "Final Mark" column).
then on query3, i just add them up.

 

[stinger]

i am doing it already in Query2. marks obtained per class and per subject (see the expression on "Final Mark" column).
then on query3, i just add them up.

 

[arnelgp]

58 there is the sum from query2

so if you want to use 200, then we will need to sum all MarksObtained?

 

[stinger]

58 there is the sum from query2

View attachment 117147

so if you want to use 200, then we will need to sum all MarksObtained?

sorry not got you, condition for final Marks is ok thats fine that is what we need, we need condition for grand total as well, there should be less or more exam type like assessment-1, assessment-2, assessment-3 and so on , if assessment-1 ,2 ,3 have marks less than 50% total marks of that exam then they should not include in grand total, same like subjects total in final marks

 

[arnelgp]

well i think you should total the MarksObtained first, which is 90.5 and show it in Assessment-1, but 90.5 is less than 100 (200 / 2).

 

[stinger]

well i think you should total the MarksObtained first, which is 90.5 and show it in Assessment-1, but 90.5 is less than 100 (200 / 2).

View attachment 117149

Thank You Very much its look very closer what i need, Thanks Dear I will See it in Detail and will come back,