Advertisement

06.30.2008 at 10:25AM PDT, ID: 23527639
[x]
Attachment Details

Build a report from two different sources

Asked by needhelpfast569 in Access Reports, Microsoft Access Database, SQL Query Syntax

I have a new report that I need to bring in data from two different tables. I'm able to read and print the report with the one table with no problem as you can see in the report example I have attached.
As you can see from the report the secondary report columns are populated correctly from the table ReportTotalsStateFile4. I need to populate the ROCP column with the data from ReportTotalsStateFileROP.
I have attached the code that I'm using for select to pull the data from the ReportTotalsStateFile4. If I could combine the two tables into on file that would solve the problem but the two files combined exceeds the 255 fields rule. I have a one-to-one relationship between the two tables joined on course code.

This may be another soultion
I have also combined the two tables into one table with records from both table combined the table is attached and called the SecondaryandROCPDataFiles with a 4 for secondary  and a 2 for ROP records in the field ReportTypeID. This way I'm just reading the one file and populating the report fields by the ReportTypeID field.

I added a dump of the combined table showing the ReportTypeID field with the 2 or 4 for the record type.


I know this may not be the best design but trying to make an existing database work. ANY help I can get to point in the correct direction would be helpful.

Thanks Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:

Private Sub Report_Open(cancel As Integer)
' Code Header inserted by the Procedure Header Add-In
'=============================================================
' Report_StateE1CBEDSDataEntryRpt.Report_Open
'-------------------------------------------------------------
' Purpose
' Author : Rick Hamlet, 09-01-2005
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' Cancel (Integer)
'-------------------------------------------------------------
' Returns:
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 09-01-2005 RJH:
'=============================================================
' End Code Header block
DoCmd.Maximize
Call ShowAccess(True, "Report")
 
'insert into ReportTotalsStateFileROP (AgencyNameROP, ReportTypeIDROP, AdultYNROP)
'select AgencyName, ReportTypeID, AdultYN from ReportTotalsStateFile2
 
 
Dim RcrdSrcStr As String
RcrdSrcStr = ""
Dim TblNameStr As String
TblNameStr = ""
 
Select Case E2RptType
'    Case "DM" this is for district reports
 
    Case "DSR" '2
        'Secondary ROCP
        TblNameStr = "2"
    Case "DSV" '4
        'Secondary
        TblNameStr = "4"
    Case "DAR" '3
        'Adult ROCP
        TblNameStr = "3"
    Case "DAV" '1
        'Adult
        TblNameStr = "1"
'    Case "SM" this is for site reports
 
    Case "SSR" '2
        'Secondary ROCP
        TblNameStr = "2"
    Case "SSV" '4
        'Secondary
        TblNameStr = "4"
    Case "SAR" '3
        'Adult ROCP
        TblNameStr = "3"
    Case "SAV" '1
        'Adult
        TblNameStr = "1"
        
End Select
 
RcrdSrcStr = "SELECT [ReportTotalsStateFile" & TblNameStr & "].[AdultYN], [ReportTotalsStateFile" & TblNameStr & "].[AgencyName], " & _
             "Val([CareerClusterAreaID]) AS ValCareerClusterAreaID, Val([CourseCode]) AS ValCourseCode,  " & _
             "[CBEDS].[CBEDSCareerCluster], [CBEDS].[CBEDS Group/Cluster], " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[TechPrepYN], " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[CareerClusterAreaID], " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[CourseCode], " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1TotParticipantsFemaleCount])AS SP1TotParticipantsFemaleCount, " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1TotParticipantsMaleCount])AS SP1TotParticipantsMaleCount, " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2TotParticipantsFemaleCount])AS SP2TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2TotParticipantsMaleCount])AS SP2TotParticipantsMaleCount," & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3TotParticipantsFemaleCount])AS SP3TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3TotParticipantsMaleCount])AS SP3TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4TotParticipantsFemaleCount])AS SP4TotParticipantsFemaleCount,  " '& _
 
Debug.Print RcrdSrcStr
 
RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4TotParticipantsMaleCount])AS SP4TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5TotParticipantsFemaleCount])AS SP5TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5TotParticipantsMaleCount])AS SP5TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6TotParticipantsFemaleCount])AS SP6TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6TotParticipantsMaleCount])AS SP6TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1TotParticipantsFemaleCount])AS EG1TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1TotParticipantsMaleCount])AS EG1TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2TotParticipantsFemaleCount])AS EG2TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2TotParticipantsMaleCount])AS EG2TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3TotParticipantsFemaleCount])AS EG3TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3TotParticipantsMaleCount])AS EG3TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4TotParticipantsFemaleCount])AS EG4TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4TotParticipantsMaleCount])AS EG4TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5TotParticipantsFemaleCount])AS EG5TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5TotParticipantsMaleCount])AS EG5TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6TotParticipantsFemaleCount])AS EG6TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6TotParticipantsMaleCount])AS EG6TotParticipantsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7TotParticipantsFemaleCount])AS EG7TotParticipantsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7TotParticipantsMaleCount])AS EG7TotParticipantsMaleCount,   "
             
RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1TotConcentratorsFemaleCount])AS SP1TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1TotConcentratorsMaleCount])AS SP1TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2TotConcentratorsFemaleCount])AS SP2TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2TotConcentratorsMaleCount])AS SP2TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3TotConcentratorsFemaleCount])AS SP3TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3TotConcentratorsMaleCount])AS SP3TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4TotConcentratorsFemaleCount])AS SP4TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4TotConcentratorsMaleCount])AS SP4TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5TotConcentratorsFemaleCount])AS SP5TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5TotConcentratorsMaleCount])AS SP5TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6TotConcentratorsFemaleCount])AS SP6TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6TotConcentratorsMaleCount])AS SP6TotConcentratorsMaleCount,  "
 
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1TotConcentratorsFemaleCount])AS EG1TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1TotConcentratorsMaleCount])AS EG1TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2TotConcentratorsFemaleCount])AS EG2TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2TotConcentratorsMaleCount])AS EG2TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3TotConcentratorsFemaleCount])AS EG3TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3TotConcentratorsMaleCount])AS EG3TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4TotConcentratorsFemaleCount])AS EG4TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4TotConcentratorsMaleCount])AS EG4TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5TotConcentratorsFemaleCount])AS EG5TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5TotConcentratorsMaleCount])AS EG5TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6TotConcentratorsFemaleCount])AS EG6TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6TotConcentratorsMaleCount])AS EG6TotConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7TotConcentratorsFemaleCount])AS EG7TotConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7TotConcentratorsMaleCount])AS EG7TotConcentratorsMaleCount,   "
             
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsFemaleCount])AS SP1Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsMaleCount])AS SP1Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsFemaleCount])AS SP2Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsMaleCount])AS SP2Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsFemaleCount])AS SP3Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsMaleCount])AS SP3Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsFemaleCount])AS SP4Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsMaleCount])AS SP4Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsFemaleCount])AS SP5Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsMaleCount])AS SP5Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsFemaleCount])AS SP6Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsMaleCount])AS SP6Tot12thConcentratorsMaleCount,  "
 
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsFemaleCount])AS EG1Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsMaleCount])AS EG1Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsFemaleCount])AS EG2Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsMaleCount])AS EG2Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsFemaleCount])AS EG3Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsMaleCount])AS EG3Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsFemaleCount])AS EG4Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsMaleCount])AS EG4Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsFemaleCount])AS EG5Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsMaleCount])AS EG5Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsFemaleCount])AS EG6Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsMaleCount])AS EG6Tot12thConcentratorsMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsFemaleCount])AS EG7Tot12thConcentratorsFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsMaleCount])AS EG7Tot12thConcentratorsMaleCount,   "
             
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsELAFemaleCount])AS SP1Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsELAMaleCount])AS SP1Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsELAFemaleCount])AS SP2Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsELAMaleCount])AS SP2Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsELAFemaleCount])AS SP3Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsELAMaleCount])AS SP3Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsELAFemaleCount])AS SP4Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsELAMaleCount])AS SP4Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsELAFemaleCount])AS SP5Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsELAMaleCount])AS SP5Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsELAFemaleCount])AS SP6Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsELAMaleCount])AS SP6Tot12thConcentratorsELAMaleCount,  "
 
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsELAFemaleCount])AS EG1Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsELAMaleCount])AS EG1Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsELAFemaleCount])AS EG2Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsELAMaleCount])AS EG2Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsELAFemaleCount])AS EG3Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsELAMaleCount])AS EG3Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsELAFemaleCount])AS EG4Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsELAMaleCount])AS EG4Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsELAFemaleCount])AS EG5Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsELAMaleCount])AS EG5Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsELAFemaleCount])AS EG6Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsELAMaleCount])AS EG6Tot12thConcentratorsELAMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsELAFemaleCount])AS EG7Tot12thConcentratorsELAFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsELAMaleCount])AS EG7Tot12thConcentratorsELAMaleCount,   "
             
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsMathFemaleCount])AS SP1Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsMathMaleCount])AS SP1Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsMathFemaleCount])AS SP2Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsMathMaleCount])AS SP2Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsMathFemaleCount])AS SP3Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsMathMaleCount])AS SP3Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsMathFemaleCount])AS SP4Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsMathMaleCount])AS SP4Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsMathFemaleCount])AS SP5Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsMathMaleCount])AS SP5Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsMathFemaleCount])AS SP6Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsMathMaleCount])AS SP6Tot12thConcentratorsMathMaleCount,  "
 
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsMathFemaleCount])AS EG1Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsMathMaleCount])AS EG1Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsMathFemaleCount])AS EG2Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsMathMaleCount])AS EG2Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsMathFemaleCount])AS EG3Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsMathMaleCount])AS EG3Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsMathFemaleCount])AS EG4Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsMathMaleCount])AS EG4Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsMathFemaleCount])AS EG5Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsMathMaleCount])AS EG5Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsMathFemaleCount])AS EG6Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsMathMaleCount])AS EG6Tot12thConcentratorsMathMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsMathFemaleCount])AS EG7Tot12thConcentratorsMathFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsMathMaleCount])AS EG7Tot12thConcentratorsMathMaleCount,   "
             
   RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsCGradeFemaleCount])AS SP1Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsCGradeMaleCount])AS SP1Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsCGradeFemaleCount])AS SP2Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsCGradeMaleCount])AS SP2Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsCGradeFemaleCount])AS SP3Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsCGradeMaleCount])AS SP3Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsCGradeFemaleCount])AS SP4Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsCGradeMaleCount])AS SP4Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsCGradeFemaleCount])AS SP5Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsCGradeMaleCount])AS SP5Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsCGradeFemaleCount])AS SP6Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsCGradeMaleCount])AS SP6Tot12thConcentratorsCGradeMaleCount,  "
 
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsCGradeFemaleCount])AS EG1Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsCGradeMaleCount])AS EG1Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsCGradeFemaleCount])AS EG2Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsCGradeMaleCount])AS EG2Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsCGradeFemaleCount])AS EG3Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsCGradeMaleCount])AS EG3Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsCGradeFemaleCount])AS EG4Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsCGradeMaleCount])AS EG4Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsCGradeFemaleCount])AS EG5Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsCGradeMaleCount])AS EG5Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsCGradeFemaleCount])AS EG6Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsCGradeMaleCount])AS EG6Tot12thConcentratorsCGradeMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsCGradeFemaleCount])AS EG7Tot12thConcentratorsCGradeFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsCGradeMaleCount])AS EG7Tot12thConcentratorsCGradeMaleCount,   "
       
   RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsDiplomaFemaleCount])AS SP1Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP1Tot12thConcentratorsDiplomaMaleCount])AS SP1Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsDiplomaFemaleCount])AS SP2Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP2Tot12thConcentratorsDiplomaMaleCount])AS SP2Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsDiplomaFemaleCount])AS SP3Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP3Tot12thConcentratorsDiplomaMaleCount])AS SP3Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsDiplomaFemaleCount])AS SP4Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP4Tot12thConcentratorsDiplomaMaleCount])AS SP4Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsDiplomaFemaleCount])AS SP5Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP5Tot12thConcentratorsDiplomaMaleCount])AS SP5Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsDiplomaFemaleCount])AS SP6Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[SP6Tot12thConcentratorsDiplomaMaleCount])AS SP6Tot12thConcentratorsDiplomaMaleCount,  "
 
  RcrdSrcStr = RcrdSrcStr & "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsDiplomaFemaleCount])AS EG1Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG1Tot12thConcentratorsDiplomaMaleCount])AS EG1Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsDiplomaFemaleCount])AS EG2Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG2Tot12thConcentratorsDiplomaMaleCount])AS EG2Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsDiplomaFemaleCount])AS EG3Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG3Tot12thConcentratorsDiplomaMaleCount])AS EG3Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsDiplomaFemaleCount])AS EG4Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG4Tot12thConcentratorsDiplomaMaleCount])AS EG4Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsDiplomaFemaleCount])AS EG5Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG5Tot12thConcentratorsDiplomaMaleCount])AS EG5Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsDiplomaFemaleCount])AS EG6Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG6Tot12thConcentratorsDiplomaMaleCount])AS EG6Tot12thConcentratorsDiplomaMaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsDiplomaFemaleCount])AS EG7Tot12thConcentratorsDiplomaFemaleCount,  " & _
             "Sum([ReportTotalsStateFile" & TblNameStr & "].[EG7Tot12thConcentratorsDiplomaMaleCount])AS EG7Tot12thConcentratorsDiplomaMaleCount   "
             
 
'Debug.Print RcrdSrcStr
Dim TotalsRptTmpIntCompQryRcrdSrcStr As String
TotalsRptTmpIntCompQryRcrdSrcStr = ""
TotalsRptTmpIntCompQryRcrdSrcStr = RcrdSrcStr & ", [ReportTotalsStateFile" & TblNameStr & "].[Ctype] "
'Debug.Print TotalsRptTmpIntCompQryRcrdSrcStr
 
Dim FromStatementStr As String
FromStatementStr = ""
 
             FromStatementStr = "FROM ReportTotalsStateFile" & TblNameStr & " INNER JOIN CBEDS " & _
             "ON [ReportTotalsStateFile" & TblNameStr & "].[CourseCode]=[CBEDS].[CBEDS Code] GROUP BY  " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[AdultYN], [ReportTotalsStateFile" & TblNameStr & "].[AgencyName],  " & _
             "[CBEDS].[CBEDSCareerCluster], [CBEDS].[CBEDS Group/Cluster], [ReportTotalsStateFile" & TblNameStr & "].[ReportTypeID], [ReportTotalsStateFile" & TblNameStr & "].[TechPrepYN],  " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[CareerClusterAreaID], [ReportTotalsStateFile" & TblNameStr & "].[CourseCode]"
             
             
RcrdSrcStr = RcrdSrcStr & FromStatementStr
'Debug.Print RcrdSrcStr
TotalsRptTmpIntCompQryRcrdSrcStr = TotalsRptTmpIntCompQryRcrdSrcStr & FromStatementStr & ", [ReportTotalsStateFile" & TblNameStr & "].[Ctype] "
'Debug.Print TotalsRptTmpIntCompQryRcrdSrcStr
RcrdSrcStr = RcrdSrcStr & "ORDER BY  " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[AdultYN], [ReportTotalsStateFile" & TblNameStr & "].[AgencyName], Val([CareerClusterAreaID]), " & _
             "Val([CourseCode]);"
 
TotalsRptTmpIntCompQryRcrdSrcStr = TotalsRptTmpIntCompQryRcrdSrcStr & "ORDER BY  " & _
             "[ReportTotalsStateFile" & TblNameStr & "].[AdultYN], [ReportTotalsStateFile" & TblNameStr & "].[AgencyName], Val([CareerClusterAreaID]), " & _
             "Val([CourseCode]);"
             
Debug.Print RcrdSrcStr
'CurrentDb.QueryDefs("TotalsRptTmpQry").SQL = RcrdSrcStr
CurrentDb.QueryDefs("TotalsReportTmpQry").SQL = RcrdSrcStr
'Debug.Print RcrdSrcStr
CurrentDb.QueryDefs("TotalsRptTmpIntCompQry").SQL = TotalsRptTmpIntCompQryRcrdSrcStr
 
'Me.RecordSource = "TotalsRptTmpQry"
Me.RecordSource = "TotalsReportTmpQry"
 
 
RcrdSrcStr = ""
 
 
End Sub
Attachments:
 
StateE1CBEDSDataEntryRpt.pdf (214 KB) (File Type Details) 
Report Results reading the one file
 
 
ReportTotalsStateFile4.pdf (49 KB) (File Type Details) 
One of the data tables used for the report
 
 
ReportTotalsStateFileROP.pdf (48 KB) (File Type Details) 
The other data table I need to bring into the report
 
 
SecondaryandROCPDataFile.pdf (48 KB) (File Type Details) 
The other option using one table with the records combined
 
 
Combinedtable.bmp (2.1 MB) (File Type Details) 
Dump of the combined table
Dump of the combined table
 
[+][-]06.30.2008 at 12:25PM PDT, ID: 21902245

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 12:38PM PDT, ID: 21902358

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 01:26PM PDT, ID: 21902743

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 01:40PM PDT, ID: 21902845

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 01:50PM PDT, ID: 21902925

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 09:27PM PDT, ID: 21904977

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 01:43PM PDT, ID: 21911446

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 01:58PM PDT, ID: 21911578

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 03:01PM PDT, ID: 21911978

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 03:16PM PDT, ID: 21912051

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Access Reports, Microsoft Access Database, SQL Query Syntax
Sign Up Now!
Solution Provided By: walterecook
Participating Experts: 2
Solution Grade: A
 
 
[+][-]07.01.2008 at 07:54PM PDT, ID: 21913104

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628