Hello,
CF = 8
DB = MySQL
Scenario:
There are 43 employees who must complete quarterly required training. There are 12 first quarterly items to complete.
I am attempting to load each of the required quarterly training items respective to each employee number, so that
subsequent reporting can determine what required training has not been completed by each employee.
Below is the code I have so far. When this is ran, it loops through the rsEmployeeNumber query and inserts 43
quarterly training records. However, it is only picking up the first record of the rsQuarterlyTraining and not the
subsequent 11 records.
Final Goal:
To query the required quarterly training requirements for a given employee and in this case display the 12
quarterly training items for the first quarter of 2013 to see what has or has not been completed.
What am I missing with my process?
<cfquery name="rsEmployeeNumber" datasource=""> <== 43 records
SELECT
record_id, last_name, first_name, emp_id, emp_id_old, assigned_shift, password, administration, emp_rank, access_group, access_status, email_address, login, exp_date, site_management, training_records, med_cert_level, med_registry_number, manage_training_events
FROM
personnel
WHERE
assigned_shift = <cfqueryparam cfsqltype="cf_sql_varchar" value="A"> or
assigned_shift = <cfqueryparam cfsqltype="cf_sql_varchar" value="B"> or
assigned_shift = <cfqueryparam cfsqltype="cf_sql_varchar" value="C">
</cfquery>
<cfloop query="rsEmployeeNumber">
<cfquery name="rsQuarterlyTraining" datasource=""> <== 12 records
SELECT RecordID, QuarterYear, QuarterNumber, TrainingGroup, GroupCode, MaterialType, TrainingTitle, TrainingCode, TrainingFile
FROM
to_quarterly_training_test
WHERE
QuarterYear = <cfqueryparam cfsqltype="cf_sql_integer" value="2013"> and
QuarterNumber = <cfqueryparam cfsqltype="cf_sql_integer" value="1"> and
MaterialType = <cfqueryparam cfsqltype="cf_sql_varchar" value="Training">
</cfquery>
<cfquery name="rsUploadTraining" datasource=""> <== Should insert 516 records
Insert Into training_completed_required_test (
trng_year
, trng_quarter
, trng_title
, emp_id)
Values (
<cfqueryparam cfsqltype="cf_sql_integer" value="#rsQuarterlyTraining.QuarterYear#">
, <cfqueryparam cfsqltype="cf_sql_integer" value="#rsQ2uarterlyTraining.QuarterNumber#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#rsQuarterlyTraining.TrainingTitle#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#rsEmployeeNumber.emp_id#">
)
</cfquery>
</cfloop>
1 Correct answer
3
Replies
AdChoices