I guess I could have I am still having an issue, it seems to work good with
the information you gave me however for some reason it will not pull the ones
that have empy data or non matching data for Section A or B, however it does
pull it for Section C and D. I even deleted the informatin out of the
tblCellData and created it in excel again and imported it back into the
system. Same issue all over again.
This is long but here is the data I have in my tblCellData.
CellID Section Cell# BED
1 A 1001 01
2 A 1002 01
3 A 1002 02
4 A 1003 01
5 A 1003 02
6 A 1004 01
7 A 1004 02
8 A 1005 01
9 A 1005 02
10 A 1006 01
11 A 1007 01
12 A 1008 01
13 A 1008 02
14 A 1009 01
15 A 1009 02
16 A 1010 01
17 A 1010 02
18 A 1011 01
19 A 1011 02
20 A 1012 01
21 A 2013 01
22 A 2013 02
23 A 2014 01
24 A 2014 02
25 A 2015 01
26 A 2015 02
27 A 2016 01
28 A 2016 02
29 A 2017 01
30 A 2017 02
31 A 2018 01
32 A 2018 02
33 A 2019 01
34 A 2019 02
35 A 2020 01
36 A 2020 02
37 A 2021 01
38 A 2021 02
39 A 2022 01
40 A 2022 02
41 A 2023 01
42 A 2023 02
43 A 2024 01
44 A 2024 02
45 B 1001 01
46 B 1002 01
47 B 1002 02
48 B 1003 01
49 B 1003 02
50 B 1004 01
51 B 1004 02
52 B 1005 01
53 B 1005 02
54 B 1006 01
55 B 1007 01
56 B 1008 01
57 B 1008 02
58 B 1009 01
59 B 1009 02
60 B 1010 01
61 B 1010 02
62 B 1011 01
63 B 1011 02
64 B 1012 01
65 B 2013 01
66 B 2013 02
67 B 2014 01
68 B 2014 02
69 B 2015 01
70 B 2015 02
71 B 2016 01
72 B 2016 02
73 B 2017 01
74 B 2017 02
75 B 2018 01
76 B 2018 02
77 B 2019 01
78 B 2019 02
79 B 2020 01
80 B 2020 02
81 B 2021 01
82 B 2021 02
83 B 2022 01
84 B 2022 02
85 B 2023 01
86 B 2023 02
87 B 2024 01
88 B 2024 02
89 C 1001 01
90 C 1002 01
91 C 1002 02
92 C 1003 01
93 C 1003 02
94 C 1004 01
95 C 1004 02
96 C 1005 01
97 C 1005 02
98 C 1006 01
99 C 1007 01
100 C 1008 01
101 C 1008 02
102 C 1009 01
103 C 1009 02
104 C 1010 01
105 C 1010 02
106 C 1011 01
107 C 1011 02
108 C 1012 01
109 C 2013 01
110 C 2013 02
111 C 2014 01
112 C 2014 02
113 C 2015 01
114 C 2015 02
115 C 2016 01
116 C 2016 02
117 C 2017 01
118 C 2017 02
119 C 2018 01
120 C 2018 02
121 C 2019 01
122 C 2019 02
123 C 2020 01
124 C 2020 02
125 C 2021 01
126 C 2021 02
127 C 2022 01
128 C 2022 02
129 C 2023 01
130 C 2023 02
131 C 2024 01
132 C 2024 02
133 D 1001 01
134 D 1002 01
135 D 1002 02
136 D 1003 01
137 D 1003 02
138 D 1004 01
139 D 1004 02
140 D 1005 01
141 D 1005 02
142 D 1006 01
143 D 1007 01
144 D 1008 01
145 D 1008 02
146 D 1009 01
147 D 1009 02
148 D 1010 01
149 D 1010 02
150 D 1011 01
151 D 1011 02
152 D 1012 01
153 D 2013 01
154 D 2013 02
155 D 2014 01
156 D 2014 02
157 D 2015 01
158 D 2015 02
159 D 2016 01
160 D 2016 02
161 D 2017 01
162 D 2017 02
163 D 2018 01
164 D 2018 02
165 D 2019 01
166 D 2019 02
167 D 2020 01
168 D 2020 02
169 D 2021 01
170 D 2021 02
171 D 2022 01
172 D 2022 02
173 D 2023 01
174 D 2023 02
175 D 2024 01
176 D 2024 02
My Results from the query (I changed inm# and Name for confidential reasons)
But as you can see it works somewhat.
Section Cell# BED IN_INMNUM IN_NAME IN_RACE IN_BLDING SEP
A 1001 01 AB1234 NAME W H
A 1002 01 AB1235 NAME B H
A 1002 02 AB1236 NAME B H
A 1003 01 AB1237 NAME W H
A 1003 02 AB1238 NAME W H
A 1004 01 AB1239 NAME W H
A 1004 02 AB1240 NAME W H
A 1005 01 AB1241 NAME B H
A 1005 02 AB1242 NAME B H
A 1006 01 AB1243 NAME B H
A 1007 01 AB1244 NAME H H
A 1008 01 AB1245 NAME W H
A 1009 01 AB1246 NAME H H
A 1009 02 AB1247 NAME B H
A 1010 01 AB1248 NAME B H
A 1010 02 AB1249 NAME B H
A 1011 02 AB1250 NAME W H
A 1012 01 AB1251 NAME W H
A 2013 01 AB1252 NAME B H
A 2014 01 AB1253 NAME W H
A 2015 01 AB1254 NAME B H
A 2016 01 AB1255 NAME W H
A 2016 02 AB1256 NAME W H
A 2017 01 AB1257 NAME B H
A 2019 01 AB1258 NAME B H
A 2020 01 AB1259 NAME W H
A 2020 02 AB1260 NAME B H
A 2021 02 AB1261 NAME B H
B 1001 01 AB1262 NAME B H
B 1002 02 AB1263 NAME B H
B 1003 02 AB1264 NAME W H
B 1004 01 AB1265 NAME B H
B 1004 02 AB1266 NAME H H
B 1005 01 AB1267 NAME W H
B 1005 02 AB1268 NAME B H
B 1006 01 AB1269 NAME B H
B 1007 01 AB1270 NAME B H
B 1008 01 AB1271 NAME B H
B 1008 02 AB1272 NAME B H
B 1009 01 AB1273 NAME B H
B 1009 02 AB1274 NAME B H
B 1010 01 AB1275 NAME B H
B 1010 02 AB1276 NAME B H
B 1011 01 AB1277 NAME B H
B 1012 01 AB1278 NAME W H
B 2013 01 AB1279 NAME B H
B 2013 02 AB1280 NAME B H
B 2014 01 AB1281 NAME B H
B 2016 01 AB1282 NAME B H
B 2017 01 AB1283 NAME B H
B 2018 01 AB1284 NAME B H
B 2019 01 AB1285 NAME H H
B 2019 02 AB1286 NAME H H
B 2020 01 AB1287 NAME B H
B 2020 02 AB1288 NAME B H
B 2021 01 AB1289 NAME B H
B 2023 01 AB1290 NAME B H
B 2024 02 AB1291 NAME W H
C 1001 01 AB1292 NAME H H
C 1002 01 AB1293 NAME B H
C 1003 01 AB1294 NAME W H
C 1003 02 AB1295 NAME W H
C 1004 01 AB1296 NAME H H
C 1004 02
C 1005 01 AB1298 NAME B H
C 1005 02 AB1299 NAME H H
C 1006 01 AB1300 NAME W H
C 1006 01 EB2698 NAME B H
C 1007 01
C 1008 01
C 1008 02 EB2701 NAME B H
C 1009 01 EB2702 NAME B H
C 1009 02
C 1010 01 EB2704 NAME B H
C 1010 02 EB2705 NAME B H
C 1011 01 EB2706 NAME B H
C 1011 02 EB2707 NAME B H
C 1012 01 EB2708 NAME B H
C 2013 01
C 2013 02 EB2710 NAME B H
C 2014 01 EB2711 NAME B H
C 2014 02
C 2015 01 EB2713 NAME B H
C 2015 02 EB2714 NAME W H
C 2016 01 EB2715 NAME B H
C 2016 02
C 2017 01
C 2017 02 EB2718 NAME H H
C 2018 01 EB2719 NAME B H
C 2018 02
C 2019 01 EB2721 NAME H H
C 2019 02
C 2020 01 EB2723 NAME B H
C 2020 02
C 2021 01 EB2725 NAME W H
C 2021 02
C 2022 01 EB2727 NAME H H
C 2022 02 EB2728 NAME B H
C 2023 01 EB2729 NAME B H
C 2023 02
C 2024 01 EB2731 NAME W H
C 2024 02
D 1001 01 EB2733 NAME W H
D 1002 01 EB2734 NAME B H
D 1002 02 EB2735 NAME B H
D 1003 01 EB2736 NAME W H
D 1003 02 EB2737 NAME W H
D 1004 01 EB2738 NAME B H
D 1004 02 EB2739 NAME B H
D 1005 01 EB2740 NAME B H
D 1005 02 EB2741 NAME W H
D 1006 01 EB2742 NAME B H
D 1007 01 EB2743 NAME B H
D 1008 01 EB2744 NAME W H
D 1008 02 EB2745 NAME W H
D 1009 01 EB2746 NAME B H
D 1009 02 EB2747 NAME B H
D 1010 01 EB2748 NAME B H
D 1010 02
D 1011 01 EB2750 NAME B H
D 1011 02 EB2751 NAME B H
D 1012 01 EB2752 NAME B H
D 2013 01 EB2753 NAME W H
D 2013 02
D 2014 01 EB2755 NAME B H
D 2014 02
D 2015 01 EB2757 NAME B H
D 2015 02
D 2016 01
D 2016 02
D 2017 01 EB2761 NAME W H
D 2017 02 EB2762 NAME W H
D 2018 01 EB2763 NAME H H
D 2018 02
D 2019 01 EB2765 NAME B H
D 2019 02
D 2020 01
D 2020 02 EB2768 NAME W H
D 2021 01 EB2769 NAME W H
D 2021 02
D 2022 01 EB2771 NAME B H
D 2022 02
D 2023 01
D 2023 02
D 2024 01 EB2775 NAME B H
D 2024 02
KARL DEWEY said:
I thought that you would have included Building the the cell table.
Michelle said:
Ok so I did that and it is not returning all of the records.
Here is my code what did I do wrong?
SELECT inmtinfo.IN_INMNUM, inmtinfo.IN_NAME, inmtinfo.IN_RACE,
inmtinfo.IN_BLDING, tblCellData.Section, tblCellData.[Cell#],
tblCellData.BED, inmtinfo.SEP
FROM inmtinfo RIGHT JOIN tblCellData ON
(inmtinfo.IN_SECTION=tblCellData.Section) AND
(inmtinfo.IN_CELLDRM=tblCellData.[Cell#]) AND
(inmtinfo.IN_BEDNUM=tblCellData.BED)
WHERE (((inmtinfo.IN_BLDING)="H"));
KARL DEWEY said:
would it be possible to add another table with all the cell information and
them have it pull if it does not have a match on the existing table.
Yes. Create a table containing the information that you allways want
displayed - Section, Cell, Bed & Sep -- name it Cell_List.
Add this table in your query and left join it to the existing table fields.
Use the fields from Cell_List instead of those from the existing table.
:
I have a report that pulls data from a querie....
The data is as follows.
Inmate #, Inmate Name, Race, Unit, Section, Cell, Bed & Sep
However it does not pull if the cell is empty say I have 30 cells in section
C but only have 10 Inmates it will only pull the 10 that cells that are used.
What I need to do is have it add the empty cells as well, however the empty
cells are not in this table (as of yet they are not in the db at all)
Because the cell data changes daily would it be possible to add another table
with all the cell information and them have it pull if it does not have a
match on the existing table.
