Need a simpler way to get various COUNTS from different tables

[Rexolio]

Working on a project for my company and I'm very LIMITED in my knowledge of ASP. Usually I can find a way to get something done, but in this particular project I need to be proficient as possible so the page loads quickly.

I need to be able to show in the web browser via ASP How many Markets each Manager is in charge of, how many NEW, ESTABLISHED or CHARTER Markets each is in charge of, how many clients each Manager is in charge of, how many clients each Market has, how many clients fall under NEW, ESTABLISHED and CHARTER Markets (hope you get the picture.)

Looking for a simple way to do this. Any ideas/suggestions would be greatly appreciated. I have included a sample of my database tables below where this information must be drawn from:

tblREGIONAL_MANAGERS
********************
ID   NAME
--   ---------------
1    John Smith
2    Bob Williams
3    Susan Jones


tblMARKET_TYPES
*******************
ID   TYPE
--   --------------
1    New
2    Established
3    Charter


tblMARKETS
****************************
ID   MGR   MARKET       TYPE
--   ---   ---------    ----
1    2     Atlanta      3 
2    1     Nashville    1
3    3     St. Louis    2
4    2     Macon        2
5    1     Memphis      3
6    3     Kansas Cty   2


tblCLIENTS
*******************
ID   MKT   NAME
--   ---   --------
1    1     J. White
2    2     B. Edwards
3    1     T. Ownes
4    3     P. Lopez
5    3     L. Morgan
6    2     E. Dole

rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[hithere]

look into SQL=SELECT COUNT. Basically you can loop through a joined rs and count how many regions a person has. I seem to remember that it works on text fields but it may do numbers as well.
hth
mb "Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian

 

[Rexolio]

I know about SELECT COUNT but not sure how to use it in this situation without doing tons of loops through each table which I don't think is necessary - has to be a better way. Do you have any examples, by chance?
rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[onpnt]

hithere I think has the best solution and I can't think of a reason why you would need to use any loops with straight sql executes
you could do this in three seperate sql executes or even one for that matter with the use of count and inner joins
and place them into functions to run by passing a integer value being TYPE to output the needed count

you can get a bunch of examples of this with a google search

http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=select+count+as+inner+join+where+asp+

some great references here

http://www.asp-help.com/database/db_sql_stmt.asp

for the best results to your questions: FAQ333-2924
Is your question a most FAQ?? Find out here FAQ333-3048

http://www.onpntwebdesigns.com/aspTut4.asp

 

[hithere]

the only example I have is pretty ugly. I had 2 rs's nested inside a 3rd. and it goes downhill from there.

One possible weird idea:

you could do a sum based on each mgr # and divide by that manager # to get your count. so if manager #2's sum was 24 and divided by 2 then your count would be 12. kinda weird but could work i suppose.

mb "Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian

 

[onpnt]

I guess if you wanted you could populate recordsets multiple times for what count you needed seperatly closing the rs each time to initialize the next count
example assuming you pass the integer value of type to the function
Function SQLCount(type)
SQL = "SELECT count(MGR) as cnt FROM tblMARKETS " & _
" WHERE TYPE=" & type
RS.Open
countMarkets = RS("cnt&quot
RS.Close
SQLCount = countMarkets
End Function

caller
SQLCount(3)

this is complete suedo concept and deffinetely will not work, just a idea.

for the best results to your questions: FAQ333-2924
Is your question a most FAQ?? Find out here FAQ333-3048

http://www.onpntwebdesigns.com/aspTut4.asp

 

[Tarwn]

This is going to require multiple queries. Basically you have a circular reference here (not in excel terms) where the information you need is to tied together to get it all in one query (at least without UNIONs).
Try doing this:
Count markets, count clients, manager name group by manager
Count markets, count clients, market_type group by market_type
Count the markets where NEW group by manager
UNION count markets where Estab group by manager
UNION count markets where Charter group by manager

This gives you (in 3 sql statements):
# of markets per specific manager (qry 1)
# of clients per specific manager (qry 1)
# of markets in each market type (qry 2)
# of clients that belong to each market type (qry 2)
# of each market type under specific manager (qry 3)

It would be possible to union all three statements, but joins would be out of the question because you would need several copies of each table in your statement and by the time that gone done joining you could have done each query seperately in a shorte amount of time.
Note: the above was pseudocode, but should be self-explanatory.

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting