Structure for query to Listbox population

[zozew]

hi guys,

im revamping (redoing) a prisonInmate DB and i need guidance in creating the VBA for it.

Data structure:

The DB has inmates in 6 security classes (MinA, MinB, Med, Max, Rdc, Jasc) then they are divided into squads (A, B, C, P1, P2 etc) that mostly fall under one of the security classes except for a few (example P1 is both under MinA and MinB) that is whats messing with my head. the squads are actual areas where they are grouped in a prison so i cant change that structure.

So the result i would like to do is a Form with a listbox for each security class listing their corresponding squads and the amount of inmates under each squad and security class something like this

______________________________

Minimum tot 125

MinA MinB
A=25 R=5
B=25 T=35
P1=15 P1=20

________________________________

Medium tot 500

Med
VD=200
VD2=150
VDX=150


_______________________________

Maximum tot 550

Max
FR=300
BT=100
BA=150

________________________________

And so on , And so on...
________________________________


I was thinking maybe a DCount FOR loop...?

Tables:

tblClass: ID, CLASS
tblSquad: ID, SQUAD
tblInmate: ID, INMATENAME, CLASS(lookup tblClass), SQUAD(lookup tblSquad)


Right now all is done with separate Quires for each of the 87 Squads and each squad has a textBox in a form for each security class so its extremely UN-dynamic, its a real pain in my bottox to add a squad or remove or anything at all....basically.

I need some guidance structuring the VBA code, as it was a while ago i did VBA in MS Access.

Thanks in advance

 

[MarkK]

It might make more sense if you had tables like . . .

tblClass: ClassID, CLASS
tblSquad: SquadID, ClassID, SQUAD
tblInmate: InmateID, SquadID, INMATENAME

In this design, it is impossible for class and squad to be in conflict at the inmate level. Also, you need to decide how distinct are MinA and MinB. I would treat them as completely distinct, and allow them both to have a squad called "P". But those P squads are then distinct. So there will be two of them, one a child of minA, the other a child of minB, and that is fine.

 

[zozew]

That's on thing I haven't been sure about... Having duplicate squads. So whenever counting or checking for the "P" squad I'll just count both MinA and MinB together.

Thanks for clearing that up :-D

Any idea on the VBA structure for counting the squads and populating a listbox?

Or if you know something that looks similar I can have a look at?