Query counting records for pre-defined ranges

[Brenda J.]

Hello all

I'm sure this is a relatively simple query to create, but I can't seem
to get my head around it.

Here is an example of the two tables I am using:

tblAccountTypes

AccountTypeID AccountType AccountNumberRange
1 Bronze between 10000 and 19999
2 Silver between 20000 and 29999
3 Gold between 30000 and 39999
4 Platinum between 40000 and 49999
5 Diamond betwee 50000 and 59999
6 Executive between 80000 and 89999
7 Family between 90000 and 99999
8 Other between 60000 and 79999 or > 99999

tblMembers

AccountNum Surname Given Name
47589 Jackson Helen
12678 Hayward Alice
97166 Bond Dee
97904 Samuel Michael
27184 Anderson Louis
16041 Janes Hanora
57909 Williams Carla
etc.

I want to count all the members for each account type. Their account
number determines what type of member they are ie. a member with the
account number 35111 would be a Gold member.

The result I am looking for would like like this:

Bronze 242
Silver 32
Gold 332
Platinum 422
Diamond 11
Executive 21
Family 255
Other 71
I can't seem to think of how to structure a query that would use the
AccountNumberRange to group the records to count them.

Any help would be greatly appreciated.

Brenda J.

[John Winterbottom]

"Brenda J." <no**********@yahoo.com> wrote in message
news:2t********************************@4ax.com...

Hello all

I'm sure this is a relatively simple query to create, but I can't seem
to get my head around it.

Here is an example of the two tables I am using:

tblAccountTypes

AccountTypeID AccountType AccountNumberRange
1 Bronze between 10000 and 19999
2 Silver between 20000 and 29999
3 Gold between 30000 and 39999
4 Platinum between 40000 and 49999
5 Diamond betwee 50000 and 59999
6 Executive between 80000 and 89999
7 Family between 90000 and 99999
8 Other between 60000 and 79999 or > 99999
tblMembers

AccountNum Surname Given Name
47589 Jackson Helen
12678 Hayward Alice
97166 Bond Dee
97904 Samuel Michael
27184 Anderson Louis
16041 Janes Hanora
57909 Williams Carla
etc.

I want to count all the members for each account type. Their account
number determines what type of member they are ie. a member with the
account number 35111 would be a Gold member.

The result I am looking for would like like this:

Bronze 242
Silver 32
Gold 332
Platinum 422
Diamond 11
Executive 21
Family 255
Other 71
I can't seem to think of how to structure a query that would use the
AccountNumberRange to group the records to count them.

Any help would be greatly appreciated.

You could make it work with some string manipulation but it would be very
very slow. Better is to add two numeric (Long) columns to tblAccountTypes
holding the starting and ending range values. Then you can join to the table
on these columns. Since the "other" account type has two ranges you will
need and extra row in the table as well.

[Bob Quintal]

Brenda J. <no**********@yahoo.com> wrote in
news:2t********************************@4ax.com:

Hello all

I'm sure this is a relatively simple query to create, but I
can't seem to get my head around it.

Here is an example of the two tables I am using:

tblAccountTypes

AccountTypeID AccountType AccountNumberRange
1 Bronze between 10000 and 19999
2 Silver between 20000 and 29999
3 Gold between 30000 and 39999
4 Platinum between 40000 and 49999
5 Diamond betwee 50000 and 59999
6 Executive between 80000 and 89999
7 Family between 90000 and 99999
8 Other between 60000 and 79999 or > 99999

tblMembers

AccountNum Surname Given Name
47589 Jackson Helen
12678 Hayward Alice
97166 Bond Dee
97904 Samuel Michael
27184 Anderson Louis
16041 Janes Hanora
57909 Williams Carla
etc.

I want to count all the members for each account type. Their
account number determines what type of member they are ie. a
member with the account number 35111 would be a Gold member.

The result I am looking for would like like this:

Bronze 242
Silver 32
Gold 332
Platinum 422
Diamond 11
Executive 21
Family 255
Other 71
I can't seem to think of how to structure a query that would
use the AccountNumberRange to group the records to count them.

Any help would be greatly appreciated.

Brenda J.

You'll have to modify the tblaccounttypes so that your low value
and high value are in separate numeric fields.

AccountTypeID AccountType LowValue HighValue
1 Bronze 10000 19999
2 Silver 20000 29999
3 Gold 30000 39999

create a query with both tables, containing fields accountNumber,
AccountType, accounttypeID. Don't join the tables.
Criteria for accountnumber is
BETWEEN [lowvalue] AND [HighValue]

Now create a totals query grouped on accountTypeID,accounttype
and count of AccountNumber.

Bob Quintal

[John Winterbottom]

"Bob Quintal" <bq******@generation.net> wrote in message
news:83******************************@news.teranew s.com...

You'll have to modify the tblaccounttypes so that your low value
and high value are in separate numeric fields.

AccountTypeID AccountType LowValue HighValue
1 Bronze 10000 19999
2 Silver 20000 29999
3 Gold 30000 39999

create a query with both tables, containing fields accountNumber,
AccountType, accounttypeID. Don't join the tables.

why would you not want to join the tables ?

[neptune]

I would have 4 fields in tblAccountTypes, with an AccountLowerLimit
field and AccountUpperLimit field. I would then count the number of
AccountNumbers in the 2nd table between these fields.

SELECT tblAccountTypes.AccountType, Count(tblMembers.AccountNum) AS
CountOfAccountNum
FROM tblAccountTypes, tblMembers
WHERE (((tblMembers.AccountNum) Between [AccountLowerLimit] And
[AccountUpperLimit]))
GROUP BY tblAccountTypes.AccountType;

[John Winterbottom]

"neptune" <bs**********@hotmail.com> wrote in message
news:c5**************************@posting.google.c om...

I would have 4 fields in tblAccountTypes, with an AccountLowerLimit
field and AccountUpperLimit field. I would then count the number of
AccountNumbers in the 2nd table between these fields.

SELECT tblAccountTypes.AccountType, Count(tblMembers.AccountNum) AS
CountOfAccountNum
FROM tblAccountTypes, tblMembers
WHERE (((tblMembers.AccountNum) Between [AccountLowerLimit] And
[AccountUpperLimit]))
GROUP BY tblAccountTypes.AccountType;

I think you might be making a lot of extra work for the query engine by
aggregating a cartesian product. It would be better to do something like
this:

select a.AccountType, Count(*) as memberCount
from tblAccountTypes as a
inner join tblMembers as m on a.
on a.AccountLowerLimit < m.AccountNum
and a.AccountUpperLimit >= m.AccountNum
group by a.AccountType

[Bob Quintal]

"John Winterbottom" <as******@hotmail.com> wrote in
news:2k*************@uni-berlin.de:

"neptune" <bs**********@hotmail.com> wrote in message
news:c5**************************@posting.google.c om...

I would have 4 fields in tblAccountTypes, with an
AccountLowerLimit field and AccountUpperLimit field. I would
then count the number of AccountNumbers in the 2nd table
between these fields.

SELECT tblAccountTypes.AccountType,
Count(tblMembers.AccountNum) AS CountOfAccountNum
FROM tblAccountTypes, tblMembers
WHERE (((tblMembers.AccountNum) Between [AccountLowerLimit]
And [AccountUpperLimit]))
GROUP BY tblAccountTypes.AccountType;

I think you might be making a lot of extra work for the query
engine by aggregating a cartesian product. It would be better
to do something like this:

select a.AccountType, Count(*) as memberCount
from tblAccountTypes as a
inner join tblMembers as m on a.
on a.AccountLowerLimit < m.AccountNum
and a.AccountUpperLimit >= m.AccountNum
group by a.AccountType

You may be right, but you can't do that in the query builder and
the OP's table design makes me think they aren't ready to write
SQL yet, and I believe that the query does some optimization. I
haven't found any slowdown when I use Neptune's syntax.

Bob Quintal

[Brenda J.]

Thank you so much, Bob - that worked like a charm.

Stupid me - I actually recall doing something very similar in an old
app, but this morning, I just couldn't recall what it was.

Brenda J.
On Thu, 24 Jun 2004 12:53:19 GMT, Bob Quintal
<bq******@generation.net> wrote:

Brenda J. <no**********@yahoo.com> wrote in
news:2t********************************@4ax.com :

Hello all

I'm sure this is a relatively simple query to create, but I
can't seem to get my head around it.

Here is an example of the two tables I am using:

tblAccountTypes

AccountTypeID AccountType AccountNumberRange
1 Bronze between 10000 and 19999
2 Silver between 20000 and 29999
3 Gold between 30000 and 39999
4 Platinum between 40000 and 49999
5 Diamond betwee 50000 and 59999
6 Executive between 80000 and 89999
7 Family between 90000 and 99999
8 Other between 60000 and 79999 or > 99999

tblMembers

AccountNum Surname Given Name
47589 Jackson Helen
12678 Hayward Alice
97166 Bond Dee
97904 Samuel Michael
27184 Anderson Louis
16041 Janes Hanora
57909 Williams Carla
etc.

I want to count all the members for each account type. Their
account number determines what type of member they are ie. a
member with the account number 35111 would be a Gold member.

The result I am looking for would like like this:

Bronze 242
Silver 32
Gold 332
Platinum 422
Diamond 11
Executive 21
Family 255
Other 71
I can't seem to think of how to structure a query that would
use the AccountNumberRange to group the records to count them.

Any help would be greatly appreciated.

Brenda J.

You'll have to modify the tblaccounttypes so that your low value
and high value are in separate numeric fields.

AccountTypeID AccountType LowValue HighValue
1 Bronze 10000 19999
2 Silver 20000 29999
3 Gold 30000 39999

create a query with both tables, containing fields accountNumber,
AccountType, accounttypeID. Don't join the tables.
Criteria for accountnumber is
BETWEEN [lowvalue] AND [HighValue]

Now create a totals query grouped on accountTypeID,accounttype
and count of AccountNumber.

Bob Quintal

[Brenda J.]

You're right, John - the string solution would probably have been
quite slow, although that was my first thought, and I had been
thinking along that line.

The two fields for low and high range did the trick. Thank!

Brenda J.
On Thu, 24 Jun 2004 08:57:58 -0400, "John Winterbottom"
<as******@hotmail.com> wrote:

"Brenda J." <no**********@yahoo.com> wrote in message
news:2t********************************@4ax.com.. .

Hello all

I'm sure this is a relatively simple query to create, but I can't seem
to get my head around it.

Here is an example of the two tables I am using:

tblAccountTypes

AccountTypeID AccountType AccountNumberRange
1 Bronze between 10000 and 19999
2 Silver between 20000 and 29999
3 Gold between 30000 and 39999
4 Platinum between 40000 and 49999
5 Diamond betwee 50000 and 59999
6 Executive between 80000 and 89999
7 Family between 90000 and 99999
8 Other between 60000 and 79999 or > 99999

tblMembers

AccountNum Surname Given Name
47589 Jackson Helen
12678 Hayward Alice
97166 Bond Dee
97904 Samuel Michael
27184 Anderson Louis
16041 Janes Hanora
57909 Williams Carla
etc.

I want to count all the members for each account type. Their account
number determines what type of member they are ie. a member with the
account number 35111 would be a Gold member.

The result I am looking for would like like this:

Bronze 242
Silver 32
Gold 332
Platinum 422
Diamond 11
Executive 21
Family 255
Other 71
I can't seem to think of how to structure a query that would use the
AccountNumberRange to group the records to count them.

Any help would be greatly appreciated.

You could make it work with some string manipulation but it would be very
very slow. Better is to add two numeric (Long) columns to tblAccountTypes
holding the starting and ending range values. Then you can join to the table
on these columns. Since the "other" account type has two ranges you will
need and extra row in the table as well.