Count groups total.. (1 Viewer)

[zozew]

Hi Ya'll

Quick query question, i need to count each group (Squad) total,

I have tree tables:

tblInmates: inmateID, inmateNames, inmateSquad, inmateClass
there are about 7000 inmateNames

tblClass: classID, className
there are 4 classNames


tblSquads: squadID, classID, squadName
there are about 80 squadNames


Each inmate has a class "RDC, Max, Med, Min" and each inmate is part of a squad (A, B C, D etc) under each class.

So the result im looking for is a table or listbox listing the names and totals of each squad under each class.

like this screen grab (attached), its now done with one line of VBA code for each textbox which is really UN-dynamical..


is that doable with one SQL statement?

Ive posted a similar question under the VBA thread as im trying to do this with VBA but i realize this part should be under Queries...


thanks for any help

 

[zozew]

update....it was a lot easier then i thought, although it could probably be done better.. one query for each subform..

for example this...

SELECT DISTINCTROW tblInmateProfile.[tbl-squad_ID], Count(*) AS [Count Of tblInmateProfile]
FROM tblInmateProfile
WHERE (((tblInmateProfile.[tbl-status_ID])=1) AND ((tblInmateProfile.[tbl-class_ID])=3))
GROUP BY tblInmateProfile.[tbl-squad_ID], tblInmateProfile.[tbl-class_ID]
ORDER BY tblInmateProfile.[tbl-class_ID];

thanks anyhow