List record by sum condition!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[Guest]

ex:

I have a table:

idex name codequestion mark
1 A MS_HR_RI1 45
2 A MS_HR_RI2 30
3 A MS_HR_RI3 15
4 A MS_HR_RI4 5
5 A MS_HR_RI5 5

----------------------------sum 100
I want to query result:

idex name codequestion mark
1 A MS_HR_RI1 45
2 A MS_HR_RI2 30


List all record when sum(mark)<80 and sum(mark)>70. How can I do that?
(mark by case is (45+30)) please help me to write query this problem.

 

[Duane Hookom]

"Sum" always suggests there is a value/field that is grouped by in the
records. You haven't provided this critical piece of information. Why in
your example are the first two records summed but not any of the others?

 

[David F Cox]

If someone does not answer your question because you have not explained it
very well and you ask it again you are likely to get someone else telling
you that you have not explained it very well. This is a waste of everybody's
time.

 

[Duane Hookom]

Good comment. I would not have replied if I had skimmed through earlier
threads.

 

[Guest]

Sorry about that because I have a problem when I try to post this question.

p/s David F Cox: Thank for your replied.

But in my question not clear for you. Now, I will explain clearly.

I have a table with colections answer (with all mark of answers is 100) .
After when I sort by desc. I want to filter all answer with top mark and have
a total mark (<80 and >70).

ex:

question mark
1 40
2 20
3 15
4 10
5 5

result:

question mark
1 40
2 20
3 15
------->it will display three questions because total mark of question equal
75 (40+20+15) <80 and >70

 

[Gary Walter]

ex:

I have a table:

idex name codequestion mark
1 A MS_HR_RI1 45
2 A MS_HR_RI2 30
3 A MS_HR_RI3 15
4 A MS_HR_RI4 5
5 A MS_HR_RI5 5

----------------------------sum 100
I want to query result:

idex name codequestion mark
1 A MS_HR_RI1 45
2 A MS_HR_RI2 30


List all record when sum(mark)<80 and sum(mark)>70. How can I do that?
(mark by case is (45+30)) please help me to write query this problem.

I'd probably create a preliminary query
that ranks the marks within each "name"
and arbitrarily uses "idex" to sort out any
ties.....
(replace "yurtable" with actual name of
your table in following query)

qryRank:

SELECT
t1.idex,
t1.[name],
t1.codequestion,
t1.mark,
(SELECT
Count(t2.mark)
FROM yurtable As t2
WHERE
t2.[name]=t1.[name]
AND
((t2.mark > t1.mark)
OR
(t2.mark=t1.mark AND t2.idex<t1.idex)) ) AS Rank
FROM yurtable AS t1;

result from your sample data:

idex [name] codequestion mark Rank
1 A MS_HR_RI1 45 0
2 A MS_HR_RI2 30 1
3 A MS_HR_RI3 15 2
4 A MS_HR_RI4 5 3
5 A MS_HR_RI5 5 4

then, use Rank to get sums for all records
less than or equal to Rank

qrySumMarksByRank

SELECT
qryRank.idex,
qryRank.[name],
qryRank.codequestion,
qryRank.mark,
qryRank.Rank,
(Select
Sum(q.mark)
FROM qryRank As q
WHERE
q.[name] = qryRank.[name]
AND
q.Rank <= qryRank.Rank) AS SumMark
FROM qryRank;

idex [name] codequestion mark Rank SumMark
1 A MS_HR_RI1 45 0 45
2 A MS_HR_RI2 30 1 75
3 A MS_HR_RI3 15 2 90
4 A MS_HR_RI4 5 3 95
5 A MS_HR_RI5 5 4 100

all that's left is to filter qrySumMarksByRank
on your criteria fro SumMark....

actually, probably not trivial, but I need to go
to work (that actually pays me)....

quick-and-dirty:

SELECT
q.idex,
q.[name],
q.codequestion,
q.mark
FROM qrySumMarksByRank As q
WHERE
q.SumMark < 80
AND
EXISTS
(SELECT
q1.idex
FROM
qrySumMarksByRank As q1
WHERE
q1.[name] = q.[name]
AND
q1.SumMark
BETWEEN
71
AND
79);