SUM CONDITION VERY DIFFICULT!!!Please help me

[Guest]

I have a table:

idex name codequestion mark
1 A MS_HR_RI1 40
2 A MS_HR_RI2 20
3 A MS_HR_RI3 15
4 A MS_HR_RI4 15


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

idex name codequestion mark
1 A MS_HR_RI1 40
2 A MS_HR_RI2 20
3 A MS_HR_RI3 15

List all record when sum(40+20+15)<80 and sum(40+20+15)>70. How can I do that?

 

[David F Cox]

It might be easier for you to do a select query with NOT "MS_HR_RI4" as a
criteria.
Use that query as an input to a group by query using SUM with a criteria >70
AND <80

 

[Guest]

NO you didn't understand what i mean!

ex:

 

[Guest]

You don't understand what i mean!

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.

 

[David F Cox]

My apologies if I am being dense.

Often it is difficult to express the problem that you are trying to solve,
and once you have conquered the difficulty of phrasing the question exactly
right seeing the solution is easy.

Do you want to see the results where any combination of two scores has a sum

70 and <80?

so we could have 6 combinations from these scores:

1 A MS_HR_RI1 45
2 A MS_HR_RI2 30
3 A MS_HR_RI3 31
4 A MS_HR_RI4 44
5 A MS_HR_RI5 32

or is it any combination of scores 20 + 25 + 30 ?

If it is any two scores you can put the same table (or query) twice on the
QBE form, joined on id. The second copy will be given a suffix bt Access
(_1).
I believe this will work (of course, use your table name instead of Test) :-

[Test.score] + [Test_1.score] will give you all combinations.
WHERE [test.codequestion] > [test_1.codequestion] will eliminate
duplicates and adding the same scores
WHERE [Test.score] + [Test_1.score] > 70 AND [Test.score] + [Test_1.score] <
70 will isolate results wanted.
(or [Test.score] + [Test_1.score] BETWEEN 71 AND 79 )?

 

[John Spencer]

Perhaps we don't understand beacuse you are not clearly stating your
requirement.

Do you want to show the records in order from 1 to 5 until the cumulative
sum is between 70 and 80?

Or do you want to show records that can be used to generate total between
70 and 80?

For instance if we rearrange the mark value in your table to
idex name codequestion mark
1 A MS_HR_RI1 5
2 A MS_HR_RI2 30
3 A MS_HR_RI3 15
4 A MS_HR_RI4 45
5 A MS_HR_RI5 6

Do we return no records since 5 + 30 + 15 = 50 and adding in 45 makes 95
which is more than 80?

Or should we return records 2 and 4 which total 75 or do we return records
1, 3, 4, and 5 which total 71?

In other words, your problem is not very clearly described.

 

[Guest]

In my table I was order by mark desc. Affter that I want to get some top
records with condition sum(total mark of some records)<80 and >70).

I alway have a table with 5 record each records is one question with mark to
itself
But total 5 question alway 100 point. I want to display all questions with
sum(all questions)<80 and >70.

some ex for clearly:
Thanks

 

[John Spencer]

Still unclear, but perhaps what you want is the show all the records for
each name where the running sum of the marks is less than 80. If so, try
the following.

SELECT *
FROM YourTable
WHERE
(
SELECT Sum(Marks)
FROM YourTable as Tmp
WHERE Tmp.Name = YourTable.Name
AND TmpIdx <=YourTable.Idx
) < 80

 

[David F Cox]

If this can be solved easily by a query it is beyond me at the moment. I
make it that there are 30 combinations of scores to be compared against the
criteria. All I can think, as first response, of is to type those into 30
columns in a query like thus:

IIF( CQ1 + CQ3 + CQ4 BETWEEN 71 AND 79, "CQ1, CQ3, CQ4","") where CQ is
short for your codequery fields.

I will think on.

 

[Guest]

Thanks Cox. You can write clearly your query.

I have 5 question each question have mark. I want to list all question which
total mark of question between (70-80). for ex: (mark is point of question)

if total mark(q1+q2) between 70-80 then list 2 question

other case

if total mark(q1+q2+q3+q4) between 70-80 then list 4 question

other case
if total mark(q1) between 70-80 then list 1 question

other case
....


Now I think it very clear.

 

[David F Cox]

I cannot come up with a sensible query to do what is required. I would
suggest tackiling this with VBA, which is a subject for another newsgroup.
Sorry, but I tried.

David F Co

 

[Neil Sunderland]

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

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.

Try creating a running total query first (replace 'mytable' to the
name of your table):

SELECT
idex, [name], codequestion, mark,
(SELECT SUM(mark)
FROM mytable AS B
WHERE A.[name] = B.[name] AND
A.codequestion <= B.codequestion) AS runtotal
FROM mytable AS A

This should give you this:
idex name codequestion mark runtotal
1 A MS_HR_RI1 45 45
2 A MS_HR_RI2 30 75
3 A MS_HR_RI3 15 90
4 A MS_HR_RI4 5 95
5 A MS_HR_RI5 5 100

Then you should what you want with this (change 'myquery' to the the
name you gave to the query above):

SELECT * FROM myquery WHERE runtotal BETWEEN 70 AND 80

idex name codequestion mark runtotal
2 A MS_HR_RI2 30 75

 

[David F Cox]

Ah, if it were that simple ....

I have 5 question each question have mark.
I want to list all question which
total mark of question between (70-80). for ex: (mark is point of
question)

if total mark(q1+q2) between 70-80 then list 2 question

other case

if total mark(q1+q2+q3+q4) between 70-80 then list 4 question

other case
if total mark(q1) between 70-80 then list 1 question

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

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.

Try creating a running total query first (replace 'mytable' to the
name of your table):

SELECT
idex, [name], codequestion, mark,
(SELECT SUM(mark)
FROM mytable AS B
WHERE A.[name] = B.[name] AND
A.codequestion <= B.codequestion) AS runtotal
FROM mytable AS A

This should give you this:
idex name codequestion mark runtotal
1 A MS_HR_RI1 45 45
2 A MS_HR_RI2 30 75
3 A MS_HR_RI3 15 90
4 A MS_HR_RI4 5 95
5 A MS_HR_RI5 5 100

Then you should what you want with this (change 'myquery' to the the
name you gave to the query above):

SELECT * FROM myquery WHERE runtotal BETWEEN 70 AND 80

idex name codequestion mark runtotal
2 A MS_HR_RI2 30 75

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[Neil Sunderland]

Ah, if it were that simple ....

I /think/ he means "list question 2", not "list 2 questions", in which
case my solution is /probably/ what he wants. But I /might/ be wrong.

I'd add a disclaimer, but there's no point now

 

[Guest]

Thank NEIL SUNDERLAND for your replie to me!

Sorry I miss something that in my table the number "id" and "codequestion"
is random and have more 700 record with different "codequestion". The result
I want is the same with you are thinking. Onething is importance that is in
my table is so many "codequestion". EX: MS_HR_MS4, MS_HR_MS_RIk3, MS_HR_Rk1
..... but I want to run total cumulative with codequestion that is end with
"MS4". How can I do that? I try with this code but it is wrong!





SELECT Index, Name, CodeQuestion, Mark, (SELECT SUM(Mark)
FROM Owner AS B
WHERE A.[Name] = B.[Name] AND A.CodeQuestion <= B.CodeQuestion) AS Runtotal
FROM Owner AS A
WHERE (CPosition="MHR") and (right([CodeQuestion],3)="MS4")
ORDER BY MARK DESC;

result:

Index Name CodeQuestion Mark Runtotal
642 AAAAAAAAAAAA HIV-RI-MS4 30 457
641 AAAAAAAAAAAA HIII-RI-MS4 15 512
640 AAAAAAAAAAAA HII-RI-MS4 15 497
645 AAAAAAAAAAAA HVII-RI-MS4 10 366
643 AAAAAAAAAAAA HV-RI-MS4 10 351
639 AAAAAAAAAAAA HI-RI-MS4 10 482
646 AAAAAAAAAAAA HVIII-RI-MS4 5 371
644 AAAAAAAAAAAA HVI-RI-MS4 5 356


result that I want is:

Index Name CodeQuestion Mark Runtotal
642 AAAAAAAAAAAA HIV-RI-MS4 30 30
641 AAAAAAAAAAAA HIII-RI-MS4 15 45
640 AAAAAAAAAAAA HII-RI-MS4 15 60
645 AAAAAAAAAAAA HVII-RI-MS4 10 70
643 AAAAAAAAAAAA HV-RI-MS4 10 80
639 AAAAAAAAAAAA HI-RI-MS4 10 90
646 AAAAAAAAAAAA HVIII-RI-MS4 5 95
644 AAAAAAAAAAAA HVI-RI-MS4 5 100

Thanks

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

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.

Try creating a running total query first (replace 'mytable' to the
name of your table):

SELECT
idex, [name], codequestion, mark,
(SELECT SUM(mark)
FROM mytable AS B
WHERE A.[name] = B.[name] AND
A.codequestion <= B.codequestion) AS runtotal
FROM mytable AS A

This should give you this:
idex name codequestion mark runtotal
1 A MS_HR_RI1 45 45
2 A MS_HR_RI2 30 75
3 A MS_HR_RI3 15 90
4 A MS_HR_RI4 5 95
5 A MS_HR_RI5 5 100

Then you should what you want with this (change 'myquery' to the the
name you gave to the query above):

SELECT * FROM myquery WHERE runtotal BETWEEN 70 AND 80

idex name codequestion mark runtotal
2 A MS_HR_RI2 30 75

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[Neil Sunderland]

Sorry I miss something that in my table the number "id" and "codequestion"
is random and have more 700 record with different "codequestion". The result
I want is the same with you are thinking. Onething is importance that is in
my table is so many "codequestion". EX: MS_HR_MS4, MS_HR_MS_RIk3, MS_HR_Rk1
.... but I want to run total cumulative with codequestion that is end with
"MS4". How can I do that? I try with this code but it is wrong!

SELECT Index, Name, CodeQuestion, Mark, (SELECT SUM(Mark)
FROM Owner AS B
WHERE A.[Name] = B.[Name] AND A.CodeQuestion <= B.CodeQuestion) AS Runtotal
FROM Owner AS A
WHERE (CPosition="MHR") and (right([CodeQuestion],3)="MS4")
ORDER BY MARK DESC;

I'm not sure I understand exactly what you want, but try this:

SELECT Index, Name, CodeQuestion, Mark,
(SELECT SUM(Mark)
FROM Owner AS B
WHERE A.[Name] = B.[Name] AND
A.CodeQuestion <= B.CodeQuestion AND
Right(A.CodeQuestion, 3) = Right(B.CodeQuestion, 3))
AS Runtotal
FROM Owner AS A
WHERE (CPosition="MHR") and (Right(A.[CodeQuestion],3)="MS4")
ORDER BY MARK DESC;

(And if that doesn't work, can you tell us what CPosition is, as it's
not been mentioned before!)

 

[Guest]

Dear "Neil Sunderland":

CPosition is a code of surveyer. I want to filter it and order "Mark" by
DESC. after that I want to cumulative sum "Mark" for ranking. From this
ranking, I want to get 70-80% "Top values" (top question high-point).

Sorry I miss something that in my table the number "id" and "codequestion"
is random and have more 700 record with different "codequestion". The result
I want is the same with you are thinking. Onething is importance that is in
my table is so many "codequestion". EX: MS_HR_MS4, MS_HR_MS_RIk3, MS_HR_Rk1
.... but I want to run total cumulative with codequestion that is end with
"MS4". How can I do that? I try with this code but it is wrong!

SELECT Index, Name, CodeQuestion, Mark, (SELECT SUM(Mark)
FROM Owner AS B
WHERE A.[Name] = B.[Name] AND A.CodeQuestion <= B.CodeQuestion) AS Runtotal
FROM Owner AS A
WHERE (CPosition="MHR") and (right([CodeQuestion],3)="MS4")
ORDER BY MARK DESC;

I'm not sure I understand exactly what you want, but try this:

SELECT Index, Name, CodeQuestion, Mark,
(SELECT SUM(Mark)
FROM Owner AS B
WHERE A.[Name] = B.[Name] AND
A.CodeQuestion <= B.CodeQuestion AND
Right(A.CodeQuestion, 3) = Right(B.CodeQuestion, 3))
AS Runtotal
FROM Owner AS A
WHERE (CPosition="MHR") and (Right(A.[CodeQuestion],3)="MS4")
ORDER BY MARK DESC;

(And if that doesn't work, can you tell us what CPosition is, as it's
not been mentioned before!)

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address