any alternative for 'partition by' in Access

S

sajid667

New member
Local time
Today, 07:38
Joined
Mar 10, 2011
Messages
7
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
 
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
 
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.
 
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.
Click to expand...

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
 
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]
 
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]
Click to expand...

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
 
sajid667 said:
total_marks_obtained: DSum("[marks_obtained]", "test_tab", "100*[marks_obtained]/[total_marks]>=33 AND Student_id = " & student_id) FROM test_tab'
Click to expand...

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]
 
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]
Click to expand...

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.
 
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))
 
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))
Click to expand...

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???
 
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.
 

Attachments

  • Show checkbox.JPG
    Show checkbox.JPG
    16.6 KB · Views: 427
You must log in or register to reply here.

Similar threads

I
VBA CODE TO SUM 5 FIELDS WITH LOWER VALUES IN ACCESS DATABASE
Replies
10
Views
342
MajP
MajP
P
Solved Showing/Adding Absent Students in Exam Results
Replies
19
Views
1,804
Pac-Man
P
brucemc777
Solved HOW??? An HTML string stored in a long text table field with breaks for variables to be used in boilerplate eMails...
Replies
10
Views
518
brucemc777
brucemc777
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
1,285
tvanstiphout
T
Uncle Gizmo
Finding Inspiration for MS Access Blogs
Replies
0
Views
401
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom