A query to return the range of a sum of values

[Jim]

I have a table that has PersonID and AmountOfMoney

eg
1 1000
2 12000
3 1500
3 2500
4 5000
4 4000
....

I want to group by to find out how much money each person has, which
is easy.
The difficult part is as follows:

I have another table as such, with 4 columns (col1rKey, col2:text,
col3 and col4:longint)

RangeID Description Min Max
1 0 - 5000 0 5000
2 5001 - 10000 5001 10000
3 10001 - 20000 10001 20000

I need a query that will give me the following output

PersonID RangeID
1 1
2 3
3 1
4 2

ie sums up the amount each person has and assigns a range to it.

I hope I've explained my problem well. I am struggling. Can anyone
help? (I'd rather not use VBA for this; just a couple of SQL
statements if possible)

Thanks
Jim

 

[Vince Stitt]

This probably isn't the best way to do this but if you
create 3 queries you can get the results you want. The
SQL for each is:

QueryTest1:
SELECT TblTest1.PersonID, Sum(TblTest1.AmountofMoney) AS
Amnt
FROM TblTest1
GROUP BY TblTest1.PersonID;

QueryTest2:
SELECT QryTest1.PersonID, QryTest1.Amnt, IIf([QryTest1].
[Amnt] Between ([TblTest2].[Min]) And ([TblTest2].[Max]),
[RangeID],"No") AS NewRngID, TblTest2.Min, TblTest2.Max,
TblTest2.RangeID
FROM QryTest1, TblTest2;

QueryTest3:
SELECT QryTest2.PersonID, QryTest2.NewRngID
FROM QryTest2
WHERE (((QryTest2.NewRngID)<>"No"))
ORDER BY QryTest2.PersonID;

Hope this helps until you get a better answer.
Vince

 

[James Shenton]

I think this will do. If I find a shorter way of doing it, I'll post it
here.

Thanks for your help
Jim.

This probably isn't the best way to do this but if you
create 3 queries you can get the results you want. The
SQL for each is:

QueryTest1:
SELECT TblTest1.PersonID, Sum(TblTest1.AmountofMoney) AS
Amnt
FROM TblTest1
GROUP BY TblTest1.PersonID;

QueryTest2:
SELECT QryTest1.PersonID, QryTest1.Amnt, IIf([QryTest1].
[Amnt] Between ([TblTest2].[Min]) And ([TblTest2].[Max]),
[RangeID],"No") AS NewRngID, TblTest2.Min, TblTest2.Max,
TblTest2.RangeID
FROM QryTest1, TblTest2;

QueryTest3:
SELECT QryTest2.PersonID, QryTest2.NewRngID
FROM QryTest2
WHERE (((QryTest2.NewRngID)<>"No"))
ORDER BY QryTest2.PersonID;

Hope this helps until you get a better answer.
Vince

-----Original Message-----
I have a table that has PersonID and AmountOfMoney

eg
1 1000
2 12000
3 1500
3 2500
4 5000
4 4000
....

I want to group by to find out how much money each person has, which
is easy.
The difficult part is as follows:

I have another table as such, with 4 columns (col1rKey, col2:text,
col3 and col4:longint)

RangeID Description Min Max
1 0 - 5000 0 5000
2 5001 - 10000 5001 10000
3 10001 - 20000 10001 20000

I need a query that will give me the following output

PersonID RangeID
1 1
2 3
3 1
4 2

ie sums up the amount each person has and assigns a range to it.

I hope I've explained my problem well. I am struggling. Can anyone
help? (I'd rather not use VBA for this; just a couple of SQL
statements if possible)

Thanks
Jim
.

 

[Chris2]

I have a table that has PersonID and AmountOfMoney

eg
1 1000
2 12000
3 1500
3 2500
4 5000
4 4000
...

I want to group by to find out how much money each person has, which
is easy.
The difficult part is as follows:

I have another table as such, with 4 columns (col1rKey, col2:text,
col3 and col4:longint)

RangeID Description Min Max
1 0 - 5000 0 5000
2 5001 - 10000 5001 10000
3 10001 - 20000 10001 20000

I need a query that will give me the following output

PersonID RangeID
1 1
2 3
3 1
4 2

SELECT P1.PersonID
,R1.RangeID
FROM People AS P1
INNER JOIN
RANGES AS R1
ON (P1.AmountOfMoney BETWEEN R1.Min and R1.Max)

This is untested, but I'm 95% sure (famous last words) it will work.


Sincerely,

Chris O.