Not sure if this can be done in access

[Guest]

I have two tables that I combine in a query for use in a form and in a report.
The two tables are as follows...
tblRHUInmtinfo
IN_INMNUM
IN_NAME
IN_RACE
IN_BLDING
IN_SECTION
IN_CELLDRM
IN_BEDNUM
BSCB
SEP

tblInspection
INSPID
BSCB
DATE
TIME
BARS
W_S
A_VENTS
W_C_F
PF_C
EF_C
CF
D_F_W_L
B_M_F
STAFF
COMMENT

SQL QUERY
SELECT tblRHUInmtinfo.IN_INMNUM, tblRHUInmtinfo.IN_NAME,
tblRHUInmtinfo.IN_RACE, tblRHUInmtinfo.IN_SECTION, tblRHUInmtinfo.BSCB,
tblRHUInmtinfo.IN_SECTION, tblInspection.DATE, tblInspection.TIME,
tblInspection.BARS, tblInspection.W_S, tblInspection.A_VENTS,
tblInspection.W_C_F, tblInspection.PF_C, tblInspection.EF_C,
tblInspection.CF, tblInspection.D_F_W_L, tblInspection.B_M_F,
tblInspection.STAFF, tblInspection.Comment
FROM tblRHUInmtinfo LEFT JOIN tblInspection ON tblRHUInmtinfo.BSCB =
tblInspection.BSCB
WHERE (((tblRHUInmtinfo.BSCB) Like "*" & [Enter Pod:] & "*"));


EXAMPLE OF DATA ON FORM
BSCB INMNUM DATE TIME INSPECION INFO STAFF COMMENT
H-A-1001-1 AB1234 current date and time (bunch of check boxes) smith test
H-A-1002-1 AY1234 current date and time (bunch of check boxes) smith test
H-A-1002-2 AZ1234 current date and time (bunch of check boxes) smith test
H-A-1003-1 current date and time (bunch of check boxes) smith
test


What I would like to see....
1 AB1234 Date - Time Check boxes Staff Name
2 AY1234 Date - Time Check boxes Staff Name
AZ1234
3 Date - Time Check boxes Staff Name

Basicly what I need to do is if they are in the same cell only show the cell
# once and then each inmate in that cell and only one set of checkboxes per
cell not per inmate.

 

[Jeff L]

You would be better off doing this in a report instead of a form. On a
report, you can group similar data items together and list the details
separately.

In your query make a field that extracts the Cell info from H-A-1001-1.
Based on your description, you want the 1001 part of H-A-1001-1. So
in your query field, put Mid(BSCB, 5,4). Now in your report, you want
to group on that field. Put your other info in the group heading as
well. Put your Inmate Numbers in the detail section.

Hope that helps!