hi there,
i have this query that inserts the values stored in the temptable back into the table with the newly associated job description...
basically what happens is that when a job description is updated, it removes the active status from that old job description and creates a new job description with an active status...this allows the old job description information to still be stored, just no longer active...
now what i'm doing is moving the old job description into the temp tables when the edit job description form is opened, and then once the update button is clicked after all edits on the form have been completed, the temptable information is then inserted into the tables and linked accordingly with the other fields from the other tables that have been modified linked to that job description...
for instance, a job description can have two responsibilities (or as many as are defined), and each responsibility then has however many functions associated to that specific responsibility...i have the temptable for the responsibilities inserting correctly so that the new job description when created is linked to those updated responsibilities, however when i insert the functions then from the temptable they are placed into the greatest value (max) of the responsibility and not into the specific ones that they are supposed to be linked to...
where am i going wrong? here is the insert query that does the functions back into the function table, which need to link up with the inserted responsibilities from the responsibilities table...but are instead just being linked to the max(pkrespID) and not each individual one as they should...
also the responsibilties insert query...
and the new job description insert...
can anyone see why the responsibilities are getting inserted correctly, but the functions are not corresponding to the correct responsibility then?
the tblResp has
pkRespID
fkJobDescripID
strRespComp
the tblFunction has
pkFunctionID
fkRespID
strFunction
and the functions should link through to the responsibility through fkRespID...
thank you so much for your time on this...i really appreciate it...
*Updated*
okay, so i tried the following and now they are inserting correctly according to what responsibility each function falls under...but they are inserting into the old job description and not the new one...any ideas on how i can get them to insert into the new one instead?
thanks...
i have this query that inserts the values stored in the temptable back into the table with the newly associated job description...
basically what happens is that when a job description is updated, it removes the active status from that old job description and creates a new job description with an active status...this allows the old job description information to still be stored, just no longer active...
now what i'm doing is moving the old job description into the temp tables when the edit job description form is opened, and then once the update button is clicked after all edits on the form have been completed, the temptable information is then inserted into the tables and linked accordingly with the other fields from the other tables that have been modified linked to that job description...
for instance, a job description can have two responsibilities (or as many as are defined), and each responsibility then has however many functions associated to that specific responsibility...i have the temptable for the responsibilities inserting correctly so that the new job description when created is linked to those updated responsibilities, however when i insert the functions then from the temptable they are placed into the greatest value (max) of the responsibility and not into the specific ones that they are supposed to be linked to...
where am i going wrong? here is the insert query that does the functions back into the function table, which need to link up with the inserted responsibilities from the responsibilities table...but are instead just being linked to the max(pkrespID) and not each individual one as they should...
Code:
strSql = ""
strSql = strSql & " INSERT INTO tblFunction ( fkRespID, strFunction )"
strSql = strSql & " SELECT (Select max(pkRespID) as MaxID from tblResp) as RespID,tblTempFunction.strFunction"
strSql = strSql & " FROM tblTempFunction "also the responsibilties insert query...
Code:
strSql = ""
strSql = strSql & " INSERT INTO tblResp ( fkJobDescripID, strRespComp, strWeight, dtmInactive )"
strSql = strSql & " SELECT (Select max(pkJobDescripID)as MaxID from tblJobDescrip) as JobDescripID, tblTempResp.strRespComp, tblTempResp.strWeight, tblTempResp.dtmInactive"
strSql = strSql & " FROM tblTempResp"and the new job description insert...
Code:
strSql = ""
strSql = strSql & " UPDATE tblJobDescrip SET tblJobDescrip.blnActive = False"
strSql = strSql & " WHERE tblJobDescrip.pkJobDescripID = " & lngpkJobDescripID
strSql = ""
strSql = strSql & " INSERT INTO tblJobDescrip ( fkJobCodeID, fkFLSAID, strPositionSummary, strWorkingConditions, "
strSql = strSql & " strPubSurvey, strStateFedSurvey, dtmUpdated, blnBargaining, blnManager, "
strSql = strSql & " blnNonManager, blnActive )"
strSql = strSql & " SELECT tblTempJobDescrip.fkJobCodeID, tblTempJobDescrip.fkFLSAID, "
strSql = strSql & " tblTempJobDescrip.strPositionSummary, tblTempJobDescrip.strWorkingConditions,"
strSql = strSql & " tblTempJobDescrip.strPubSurvey, tblTempJobDescrip.strStateFedSurvey, tblTempJobDescrip.dtmUpdated,"
strSql = strSql & " tblTempJobDescrip.blnBargaining, tblTempJobDescrip.blnManager, tblTempJobDescrip.blnNonManager,"
strSql = strSql & " tblTempJobDescrip.blnActive "
strSql = strSql & " FROM tblTempJobDescrip"can anyone see why the responsibilities are getting inserted correctly, but the functions are not corresponding to the correct responsibility then?
the tblResp has
pkRespID
fkJobDescripID
strRespComp
the tblFunction has
pkFunctionID
fkRespID
strFunction
and the functions should link through to the responsibility through fkRespID...
thank you so much for your time on this...i really appreciate it...
*Updated*
okay, so i tried the following and now they are inserting correctly according to what responsibility each function falls under...but they are inserting into the old job description and not the new one...any ideas on how i can get them to insert into the new one instead?
thanks...
Code:
strSql = ""
strSql = strSql & " INSERT INTO tblFunction ( fkRespID, strFunction )"
strSql = strSql & " SELECT tblTempResp.pkRespID, tblTempFunction.strFunction"
strSql = strSql & " FROM tblTempResp INNER JOIN tblTempFunction ON tblTempResp.pkRespID = tblTempFunction.fkRespID"
