any alternative for 'partition by' in Access (1 Viewer)

sajid667 · Mar 10, 2011

i need some help in an sql query in Access 2007 although i have done it in Oracle . to understand the problem you have to take the pain of reading this whole thing

i have a table in this format

student_id | subject | total_marks | marks_obtained
1 Math 100 40
1 English 150 54
1 Physics 75 45
2 Economis 100 56
2 Maths 100 43
3 Philosophy 100 25
...........

-students can have different combination of subjects.
-subjects can have different total marks.

i want to run a query that return me the above table plus an additional field total_marks_obtained as in the following format

student_id | subject | total_marks | marks_obtained | total_marks_obtained

under the field of total_marks_obtained, i should get the sum of obtained_marks(of all the subjects-a student appeared in exam for) of only those students who get passed.

Passing criteria: only those students are considered as passed who secured equal to or greater than 33% marks in every subject he/she appeared in exam for.i.e, any student who obtained less than 33% marks in one or more subject is considered as fail.

in Oracle i was able to get the desired result in the following format.

select student_id, subject, marks_obtained,
case
when min (100 * marks_obtained / total_marks) over
(partition by student_id) >= 33
then sum (marks_obtained) over
(partition by student_id)
else null
end as total_marks_obtained
from test_tab

will appreciate your cooperation in this regard

vbaInet · Mar 10, 2011

How about a DSum() with one of the criteria being obtained_marks => 33?

http://www.techonthenet.com/access/functions/domain/dsum.php

sajid667 · Mar 11, 2011

i studied DSUM function and searched it on net..i am not sure if it can serve my purpose. i try to use it with SELECT but it isnt working

vbaInet · Mar 11, 2011

Show us what you've written (i.e. the DSum statement) and we will tell you how to put it right.

Also mention the datatypes of the field(s) you've used in the DSum() function as well.

sajid667 · Mar 12, 2011

vbaInet said:
Show us what you've written (i.e. the DSum statement) and we will tell you how to put it right.

Also mention the datatypes of the field(s) you've used in the DSum() function as well.

i tried this query

SELECT student_id, subject, total_marks, marks_obtained,total_marks_obtained= DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33") from test_tab

vbaInet · Mar 12, 2011

Almost there, it should be:

Code:

SELECT student_id, subject, total_marks,  marks_obtained, total_marks_obtained[COLOR=Red]:[/COLOR] DSum("[marks_obtained]",  "test_tab", "100*[marks_obtained]/[total_marks]>=33 [COLOR=Red]AND Student_id = " & student_id[/COLOR]) FROM test_tab;

An equal to sign (=) should not precede the DSum() as you saw in the example. That is only when you're using it in the Control Source property of a control (like a textbox for example).

By the way, I thought you said you were calculating for marks_obtained greater than 33?

If the data type of student_id is Text then change this part:

Code:

[COLOR=Red]= " & student_id[/COLOR]

to this:

Code:

[COLOR=Red]= [COLOR=Blue]'[/COLOR]" & student_id & "[COLOR=Blue]'[/COLOR]"[/COLOR]

sajid667 · Mar 14, 2011

vbaInet said:
Almost there, it should be:

Code:

SELECT student_id, subject, total_marks, marks_obtained, total_marks_obtained[COLOR=Red]:[/COLOR] DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33 [COLOR=Red]AND Student_id = " & student_id[/COLOR]) FROM test_tab;

An equal to sign (=) should not precede the DSum() as you saw in the example. That is only when you're using it in the Control Source property of a control (like a textbox for example).

By the way, I thought you said you were calculating for marks_obtained greater than 33?

If the data type of student_id is Text then change this part:

Code:

[COLOR=Red]= " & student_id[/COLOR]

to this:

Code:

[COLOR=Red]= [COLOR=Blue]'[/COLOR]" & student_id & "[COLOR=Blue]'[/COLOR]"[/COLOR]

i tried the query but it is giving when i run it, it gives the following error

Syntax error (missing operator) in query expression 'total_marks_obtained: DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33 AND Student_id = " & student_id) FROM test_tab'.

looking forward for your help as my last hope

Galaxiom · Mar 14, 2011

sajid667 said:
total_marks_obtained: DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33 AND Student_id = " & student_id) FROM test_tab'

Wait up. That aint SQL.

Code:

DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33 AND Student_id = " & student_id) [COLOR=blue]AS total_marks_obtained[/COLOR]

sajid667 · Mar 14, 2011

Galaxiom said:
Wait up. That aint SQL.

Code:

DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33 AND Student_id = " & student_id) [COLOR=blue]AS total_marks_obtained[/COLOR]

it almost has worked but there is a little problem. it is showing me the sum of marks obtained of all the students for the subjects in which they get passed while i want the total_marks_obtained of only those students who get passed in every subject in which they appeared for.

for ecample if i have this table

test_tab
student_id | subject | total_marks | marks_obtained
1 | Maths | 100 | 40
1 | English | 150 | 54
1 | Physics | 75 | 45
2 | Economics | 100 | 21
2 | Maths | 100 | 43
3 | Maths | 100 | 90

your query give me the following result

Query1

student_id | subject | total_marks | marks_obtained | total_marks_obtained
1 | Maths | 100 | 40 | 139
1 | English | 150 | 54 | 139
1 | Physics | 75 | 45 | 139
2 | Economics | 100 | 21 | 43
2 | Maths | 100 | 43 | 43
3 | Maths | 100 | 90 | 90

while i want the following result

Query1

student_id | subject | total_marks | marks_obtained | total_marks_obtained
1 | Maths | 100 | 40 | 139
1 | English | 150 | 54 | 139
1 | Physics | 75 | 45 | 139
2 | Economics | 100 | 21 | nill
2 | Maths | 100 | 43 | nill
3 | Maths | 100 | 90 | 90

i.e, it should give me the nill or a blank in total_marks_obtained for the students who got failed in one or mare subjects.

vbaInet · Mar 14, 2011

Ah, I see what you're getting at. So you need three columns in your query.

1. A count of how many subjects the pupil takes:

Code:

Subj_Count: (SELECT Count(*) FROM test_tab As Q WHERE Q.student_id = test_tab.student_id)

2. A count of the marks per student that is greater than 33%:

Code:

Subj33_Count: (SELECT Count(*) FROM test_tab AS Q WHERE Q.student_id = test_tab.student_id AND ((Q.[marks_obtained]/Q.[total_marks]) * 100) >= 33)

3. The final column to get the SUMs:

Code:

total_marks_obtained: IIF(Subj_Count <> Subj33_Count, Null, (SELECT Sum(Q.[marks_obtained]) FROM test_tab AS Q WHERE Q.student_id = test_tab.student_id))

sajid667 · Mar 15, 2011

vbaInet said:
Ah, I see what you're getting at. So you need three columns in your query.

1. A count of how many subjects the pupil takes:

Code:

Subj_Count: (SELECT Count(*) FROM test_tab As Q WHERE Q.student_id = test_tab.student_id)

2. A count of the marks per student that is greater than 33%:

Code:

Subj33_Count: (SELECT Count(*) FROM test_tab AS Q WHERE Q.student_id = test_tab.student_id AND ((Q.[marks_obtained]/Q.[total_marks]) * 100) >= 33)

3. The final column to get the SUMs:

Code:

total_marks_obtained: IIF(Subj_Count <> Subj33_Count, Null, (SELECT Sum(Q.[marks_obtained]) FROM test_tab AS Q WHERE Q.student_id = test_tab.student_id))

Thank you so very much as using these codes in my query, i was able to get the required result.here is how i write my query

SELECT student_id,subject, total_marks,marks_obtained, (SELECT Count(*) FROM test_tab As Q WHERE Q.student_id = test_tab.student_id) AS Subj_Count, (SELECT Count(*) FROM test_tab AS Q WHERE Q.student_id = test_tab.student_id AND ((Q.[marks_obtained]/Q.[total_marks]) * 100) >= 33) AS Subj33_Count, IIF(Subj_Count <> Subj33_Count, Null, (SELECT Sum(Q.[marks_obtained]) FROM test_tab AS Q WHERE Q.student_id = test_tab.student_id)) AS total_marks_obtained FROM test_tab;

there is only one small problem to tackle. and that is; although i will need Subj_Count and Subj33_Count to calculate total_marks_obtained according to the given criteria; but i dont want to display these two columns in the query result. i only want to display total_marks_obtained as an additional column along with the columns of original table in the following format as i described in my previous post.

student_id | subject | total_marks | marks_obtained | total_marks_obtained

is there any way around???

vbaInet · Mar 15, 2011

One of two ways:

1. Untick the Show check box. See attached image.

OR

2.Create a new query based on this query and pull in only the fields you require.

sajid667 · Mar 17, 2011

vbaInet said:
One of two ways:

1. Untick the Show check box. See attached image.

OR

2.Create a new query based on this query and pull in only the fields you require.

thanks.got it.

any alternative for 'partition by' in Access (1 Viewer)

sajid667

New member

vbaInet

AWF VIP

sajid667

New member

vbaInet

AWF VIP

sajid667

New member

vbaInet

AWF VIP

sajid667

New member

Galaxiom

Super Moderator

sajid667

New member

vbaInet

AWF VIP

sajid667

New member

vbaInet

AWF VIP

Attachments

sajid667

New member

Similar threads

Users who are viewing this thread