Query Help Needed (1 Viewer)

[melodyF]

Need some advice on building a query..

I'm utilizing 2 tables: Region,AccountData

The region table contains 35 regions.I'm using Prin_Bal and SScore fields from AccountData. The AccountData table contains around 50,000 accounts each containing a prin_bal and SScore. I need to sum the prin_bal for each region utilizing a range of SScore. I'm using 6 ranges. 0-124,125-144,etc.. I want to try to get output similiar to the below.

0-124 125-144 etc..
Region1 $50,000 $66,000
Region2 $100,000 $75,000
Region3

Any help would be appreciated,

Thanks
FM

 

[Pat Hartman]

Create a table to hold your Score groups:

tblScoreGroup
GroupID (autonumber, primary key)
GroupCd (text, Unique Index1)
BeginGroup (long integer, Unique Index2 fld1)
EndGroup (long integer, Unique Index2 fld2)

Some values:
1, "0-124", 0, 124
2, "125-144", 125, 144
3, etc.
99, "1000-and up", 1000, 999999999

Create a query that joins to this table:
query1:
Select AccountData.region, AccountData.Prin_Bal, AccountData.SScore from AccountData Inner Join tblScoreGroup as g On AccountData.SScore >= g.BeginGroup And AccountData.SScore <= g.EndGroup;

Note that the above query CANNOT be displayed in QBE view because it's join is not an equi-join. You can only display the query in SQL view.

Then create a crosstab based on query1.