Am I doing this correctly?

M

martinmike2

Hello,

I am wondering if I am doing this correctly or not due to some odd
occurences after completiojn of the below procedure.


What we are trying to do is update our tables with another table. I
know it sounds simple enough, and it seems to work for us, except
everytime we do this procedure we have to go back to our main table
and correct some errors.

Step 1: Recieve and import a table called EDVR (This imports as EDVR2)

Step 2: Append EDVR2 to EDVR1 to remove duplicates
INSERT INTO EDVR1
SELECT DISTINCT EDVR2.*
FROM EDVR2;

Step 3: Add new SSN's to SSN table (Ignore PL as these are planned
losses)
INSERT INTO SSNs ( Actual_SSN )
SELECT EDVR1.SSN
FROM EDVR1 LEFT JOIN SSNs ON EDVR1.SSN = SSNs.Actual_SSN
WHERE (((SSNs.Actual_SSN) Is Null) AND ((EDVR1.ACCT_CAT)<>"pl"));

Step 4: Update SSN1 field of EDVR1 to show AutoNumber value associated
with SSN's in SSN table

UPDATE EDVR1 INNER JOIN SSNs ON EDVR1.SSN = SSNs.Actual_SSN SET
EDVR1.SSN1 = SSNs.SSN;

Step 5: Add any records from EDVR1 to EDVR that are not in EDVR
INSERT INTO EDVR ( SPECAT, DESIG, LIM_DUTY, NAME, TAR_IND, A_RATE_ABR,
SDAP, D_RATE_ABR, DNEC1, DNEC2, SPI, PNEC, SNEC, CITIZ, SEX, EREN,
EFM, PRI_DEP, SEC_DEP, DOS, ADSD, BR_CL, EAOS, SCOL_EXT, OTH_EXT,
OPEX, PRD, SDCD, DATE_RECD, ACCT_CAT, EDA_EDL, PG_PL_AUTH, [SECTION],
DTY_STA_DT, UIC_TO_FRM, V_RMKS1, V_RMKS2, V_RMKS3, V_RMKS4, V_RMKS5,
A_RATE_CD, S_S, CMP, MCA, POB_START, POB_STOP, POB_CAT3, COB, TNEC,
UIC, D_RATE_CD, A_FILLER1, ARC, ACT_SHT_TI, QTNEC, QNNEC, A_FILLER2,
CW_UIC, CW_SECT, CW_PART, A_FILLER3, TYPE_INVST, INVEST_DT,
CLEAR_ELIG, CLEAR_AUTH, GRANTED_DT, INVOL_EXTS, PEBD, TIR, ADV_EFF_DT,
FORMAN_STA, FORMAN_DT, MIL_RANK, WORK_CTR, STATUS, MOD_DATE, MODIFY,
FLAG, SSN )
SELECT EDVR1.SPECAT, EDVR1.DESIG, EDVR1.LIM_DUTY, EDVR1.NAME,
EDVR1.TAR_IND, EDVR1.A_RATE_ABR, EDVR1.SDAP, EDVR1.D_RATE_ABR,
EDVR1.DNEC1, EDVR1.DNEC2, EDVR1.SPI, EDVR1.PNEC, EDVR1.SNEC,
EDVR1.CITIZ, EDVR1.SEX, EDVR1.EREN, EDVR1.EFM, EDVR1.PRI_DEP,
EDVR1.SEC_DEP, EDVR1.DOS, EDVR1.ADSD, EDVR1.BR_CL, EDVR1.EAOS,
EDVR1.SCOL_EXT, EDVR1.OTH_EXT, EDVR1.OPEX, EDVR1.PRD, EDVR1.SDCD,
EDVR1.DATE_RECD, EDVR1.ACCT_CAT, EDVR1.EDA_EDL, EDVR1.PG_PL_AUTH,
EDVR1.SECTION, EDVR1.DTY_STA_DT, EDVR1.UIC_TO_FRM, EDVR1.V_RMKS1,
EDVR1.V_RMKS2, EDVR1.V_RMKS3, EDVR1.V_RMKS4, EDVR1.V_RMKS5,
EDVR1.A_RATE_CD, EDVR1.S_S, EDVR1.CMP, EDVR1.MCA, EDVR1.POB_START,
EDVR1.POB_STOP, EDVR1.POB_CAT3, EDVR1.COB, EDVR1.TNEC, EDVR1.UIC,
EDVR1.D_RATE_CD, EDVR1.A_FILLER1, EDVR1.ARC, EDVR1.ACT_SHT_TI,
EDVR1.QTNEC, EDVR1.QNNEC, EDVR1.A_FILLER2, EDVR1.CW_UIC,
EDVR1.CW_SECT, EDVR1.CW_PART, EDVR1.A_FILLER3, EDVR1.TYPE_INVST,
EDVR1.INVEST_DT, EDVR1.CLEAR_ELIG, EDVR1.CLEAR_AUTH, EDVR1.GRANTED_DT,
EDVR1.INVOL_EXTS, EDVR1.PEBD, EDVR1.TIR, EDVR1.ADV_EFF_DT,
EDVR1.FORMAN_STA, EDVR1.FORMAN_DT, EDVR1.MIL_RANK, EDVR1.WORK_CTR,
EDVR1.STATUS, EDVR1.MOD_DATE, EDVR1.MODIFY, EDVR1.FLAG, EDVR1.SSN1
FROM EDVR1 LEFT JOIN EDVR ON EDVR1.SSN1 = EDVR.SSN
WHERE (((EDVR.SSN) Is Null));

Step 5: Update records in EDVR with values in EDVR1

UPDATE EDVR INNER JOIN EDVR1 ON EDVR.SSN = EDVR1.SSN1 SET EDVR.SPECAT
= EDVR1.SPECAT, EDVR.DESIG = EDVR1.DESIG, EDVR.LIM_DUTY =
EDVR1.LIM_DUTY, EDVR.NAME = EDVR1.NAME, EDVR.TAR_IND = EDVR1.TAR_IND,
EDVR.A_RATE_ABR = EDVR1.A_RATE_ABR, EDVR.SDAP = EDVR1.SDAP,
EDVR.D_RATE_ABR = EDVR1.D_RATE_ABR, EDVR.DNEC1 = EDVR1.DNEC1,
EDVR.DNEC2 = EDVR1.DNEC2, EDVR.SPI = EDVR1.SPI, EDVR.PNEC =
EDVR1.PNEC, EDVR.SNEC = EDVR1.SNEC, EDVR.CITIZ = EDVR1.CITIZ, EDVR.SEX
= EDVR1.SEX, EDVR.EREN = EDVR1.EREN, EDVR.EFM = EDVR1.EFM,
EDVR.PRI_DEP = EDVR1.PRI_DEP, EDVR.SEC_DEP = EDVR1.SEC_DEP, EDVR.DOS =
EDVR1.DOS, EDVR.ADSD = EDVR1.ADSD, EDVR.BR_CL = EDVR1.BR_CL, EDVR.EAOS
= EDVR1.EAOS, EDVR.SCOL_EXT = EDVR1.SCOL_EXT, EDVR.OTH_EXT =
EDVR1.OTH_EXT, EDVR.OPEX = EDVR1.OPEX, EDVR.PRD = EDVR1.PRD, EDVR.SDCD
= EDVR1.SDCD, EDVR.DATE_RECD = EDVR1.DATE_RECD, EDVR.ACCT_CAT =
EDVR1.ACCT_CAT, EDVR.EDA_EDL = EDVR1.EDA_EDL, EDVR.PG_PL_AUTH =
EDVR1.PG_PL_AUTH, EDVR.[SECTION] = EDVR1.SECTION, EDVR.DTY_STA_DT =
EDVR1.DTY_STA_DT, EDVR.UIC_TO_FRM = EDVR1.UIC_TO_FRM, EDVR.V_RMKS1 =
EDVR1.V_RMKS1, EDVR.V_RMKS2 = EDVR1.V_RMKS2, EDVR.V_RMKS3 =
EDVR1.V_RMKS3, EDVR.V_RMKS4 = EDVR1.V_RMKS4, EDVR.V_RMKS5 =
EDVR1.V_RMKS5, EDVR.A_RATE_CD = EDVR1.A_RATE_CD, EDVR.S_S = EDVR1.S_S,
EDVR.CMP = EDVR1.CMP, EDVR.MCA = EDVR1.MCA, EDVR.POB_START =
EDVR1.POB_START, EDVR.POB_STOP = EDVR1.POB_STOP, EDVR.POB_CAT3 =
EDVR1.POB_CAT3, EDVR.COB = EDVR1.COB, EDVR.TNEC = EDVR1.TNEC, EDVR.UIC
= EDVR1.UIC, EDVR.D_RATE_CD = EDVR1.D_RATE_CD, EDVR.A_FILLER1 =
EDVR1.A_FILLER1, EDVR.ARC = EDVR1.ARC, EDVR.ACT_SHT_TI =
EDVR1.ACT_SHT_TI, EDVR.QTNEC = EDVR1.QTNEC, EDVR.QNNEC = EDVR1.QNNEC,
EDVR.A_FILLER2 = EDVR1.A_FILLER2, EDVR.CW_UIC = EDVR1.CW_UIC,
EDVR.CW_SECT = EDVR1.CW_SECT, EDVR.CW_PART = EDVR1.CW_PART,
EDVR.A_FILLER3 = EDVR1.A_FILLER3, EDVR.TYPE_INVST = EDVR1.TYPE_INVST,
EDVR.INVEST_DT = EDVR1.INVEST_DT, EDVR.CLEAR_ELIG = EDVR1.CLEAR_ELIG,
EDVR.CLEAR_AUTH = EDVR1.CLEAR_AUTH, EDVR.GRANTED_DT =
EDVR1.GRANTED_DT, EDVR.INVOL_EXTS = EDVR1.INVOL_EXTS, EDVR.PEBD =
EDVR1.PEBD, EDVR.TIR = EDVR1.TIR, EDVR.ADV_EFF_DT = EDVR1.ADV_EFF_DT,
EDVR.FORMAN_STA = EDVR1.FORMAN_STA, EDVR.FORMAN_DT = EDVR1.FORMAN_DT,
EDVR.MIL_RANK = EDVR1.MIL_RANK, EDVR.WORK_CTR = EDVR1.WORK_CTR,
EDVR.STATUS = EDVR1.STATUS, EDVR.MOD_DATE = EDVR1.MOD_DATE,
EDVR.MODIFY = EDVR1.MODIFY, EDVR.FLAG = EDVR1.FLAG, EDVR.SSN =
EDVR1.SSN1;

Step 6: Delete any records in EDVR that are not in EDVR1

DELETE EDVR.*, Exists (SELECT EDVR1.SSN1 FROM EDVR1
WHERE EDVR.SSN = EDVR1.SSN1) AS Expr2, EDVR.ACCT_CAT
FROM EDVR
WHERE (((Exists (SELECT EDVR1.SSN1 FROM EDVR1
WHERE EDVR.SSN = EDVR1.SSN1))=False) AND ((EDVR.ACCT_CAT)<>"pg"));

Step 7: Delete all records from EDVR1 (intermediate table)

DELETE EDVR1.*
FROM EDVR1

Step 8: Delete EDVR2 (Accomplished in Code)





What I am wondering is, is this the correct way to accomplish these
tasks? If not I would appreciate help on the subject. If there is a
more efficient way to accomplish this, then that would be great help
to.

This is a MDB and not a project.


Sincerely,
Michael Martin
 
G

Guest

Looks good to me: that is the way I do it. What kind of
problems are you having?

(david)

martinmike2 said:
Hello,

I am wondering if I am doing this correctly or not due to some odd
occurences after completiojn of the below procedure.


What we are trying to do is update our tables with another table. I
know it sounds simple enough, and it seems to work for us, except
everytime we do this procedure we have to go back to our main table
and correct some errors.

Step 1: Recieve and import a table called EDVR (This imports as EDVR2)

Step 2: Append EDVR2 to EDVR1 to remove duplicates
INSERT INTO EDVR1
SELECT DISTINCT EDVR2.*
FROM EDVR2;

Step 3: Add new SSN's to SSN table (Ignore PL as these are planned
losses)
INSERT INTO SSNs ( Actual_SSN )
SELECT EDVR1.SSN
FROM EDVR1 LEFT JOIN SSNs ON EDVR1.SSN = SSNs.Actual_SSN
WHERE (((SSNs.Actual_SSN) Is Null) AND ((EDVR1.ACCT_CAT)<>"pl"));

Step 4: Update SSN1 field of EDVR1 to show AutoNumber value associated
with SSN's in SSN table

UPDATE EDVR1 INNER JOIN SSNs ON EDVR1.SSN = SSNs.Actual_SSN SET
EDVR1.SSN1 = SSNs.SSN;

Step 5: Add any records from EDVR1 to EDVR that are not in EDVR
INSERT INTO EDVR ( SPECAT, DESIG, LIM_DUTY, NAME, TAR_IND, A_RATE_ABR,
SDAP, D_RATE_ABR, DNEC1, DNEC2, SPI, PNEC, SNEC, CITIZ, SEX, EREN,
EFM, PRI_DEP, SEC_DEP, DOS, ADSD, BR_CL, EAOS, SCOL_EXT, OTH_EXT,
OPEX, PRD, SDCD, DATE_RECD, ACCT_CAT, EDA_EDL, PG_PL_AUTH, [SECTION],
DTY_STA_DT, UIC_TO_FRM, V_RMKS1, V_RMKS2, V_RMKS3, V_RMKS4, V_RMKS5,
A_RATE_CD, S_S, CMP, MCA, POB_START, POB_STOP, POB_CAT3, COB, TNEC,
UIC, D_RATE_CD, A_FILLER1, ARC, ACT_SHT_TI, QTNEC, QNNEC, A_FILLER2,
CW_UIC, CW_SECT, CW_PART, A_FILLER3, TYPE_INVST, INVEST_DT,
CLEAR_ELIG, CLEAR_AUTH, GRANTED_DT, INVOL_EXTS, PEBD, TIR, ADV_EFF_DT,
FORMAN_STA, FORMAN_DT, MIL_RANK, WORK_CTR, STATUS, MOD_DATE, MODIFY,
FLAG, SSN )
SELECT EDVR1.SPECAT, EDVR1.DESIG, EDVR1.LIM_DUTY, EDVR1.NAME,
EDVR1.TAR_IND, EDVR1.A_RATE_ABR, EDVR1.SDAP, EDVR1.D_RATE_ABR,
EDVR1.DNEC1, EDVR1.DNEC2, EDVR1.SPI, EDVR1.PNEC, EDVR1.SNEC,
EDVR1.CITIZ, EDVR1.SEX, EDVR1.EREN, EDVR1.EFM, EDVR1.PRI_DEP,
EDVR1.SEC_DEP, EDVR1.DOS, EDVR1.ADSD, EDVR1.BR_CL, EDVR1.EAOS,
EDVR1.SCOL_EXT, EDVR1.OTH_EXT, EDVR1.OPEX, EDVR1.PRD, EDVR1.SDCD,
EDVR1.DATE_RECD, EDVR1.ACCT_CAT, EDVR1.EDA_EDL, EDVR1.PG_PL_AUTH,
EDVR1.SECTION, EDVR1.DTY_STA_DT, EDVR1.UIC_TO_FRM, EDVR1.V_RMKS1,
EDVR1.V_RMKS2, EDVR1.V_RMKS3, EDVR1.V_RMKS4, EDVR1.V_RMKS5,
EDVR1.A_RATE_CD, EDVR1.S_S, EDVR1.CMP, EDVR1.MCA, EDVR1.POB_START,
EDVR1.POB_STOP, EDVR1.POB_CAT3, EDVR1.COB, EDVR1.TNEC, EDVR1.UIC,
EDVR1.D_RATE_CD, EDVR1.A_FILLER1, EDVR1.ARC, EDVR1.ACT_SHT_TI,
EDVR1.QTNEC, EDVR1.QNNEC, EDVR1.A_FILLER2, EDVR1.CW_UIC,
EDVR1.CW_SECT, EDVR1.CW_PART, EDVR1.A_FILLER3, EDVR1.TYPE_INVST,
EDVR1.INVEST_DT, EDVR1.CLEAR_ELIG, EDVR1.CLEAR_AUTH, EDVR1.GRANTED_DT,
EDVR1.INVOL_EXTS, EDVR1.PEBD, EDVR1.TIR, EDVR1.ADV_EFF_DT,
EDVR1.FORMAN_STA, EDVR1.FORMAN_DT, EDVR1.MIL_RANK, EDVR1.WORK_CTR,
EDVR1.STATUS, EDVR1.MOD_DATE, EDVR1.MODIFY, EDVR1.FLAG, EDVR1.SSN1
FROM EDVR1 LEFT JOIN EDVR ON EDVR1.SSN1 = EDVR.SSN
WHERE (((EDVR.SSN) Is Null));

Step 5: Update records in EDVR with values in EDVR1

UPDATE EDVR INNER JOIN EDVR1 ON EDVR.SSN = EDVR1.SSN1 SET EDVR.SPECAT
= EDVR1.SPECAT, EDVR.DESIG = EDVR1.DESIG, EDVR.LIM_DUTY =
EDVR1.LIM_DUTY, EDVR.NAME = EDVR1.NAME, EDVR.TAR_IND = EDVR1.TAR_IND,
EDVR.A_RATE_ABR = EDVR1.A_RATE_ABR, EDVR.SDAP = EDVR1.SDAP,
EDVR.D_RATE_ABR = EDVR1.D_RATE_ABR, EDVR.DNEC1 = EDVR1.DNEC1,
EDVR.DNEC2 = EDVR1.DNEC2, EDVR.SPI = EDVR1.SPI, EDVR.PNEC =
EDVR1.PNEC, EDVR.SNEC = EDVR1.SNEC, EDVR.CITIZ = EDVR1.CITIZ, EDVR.SEX
= EDVR1.SEX, EDVR.EREN = EDVR1.EREN, EDVR.EFM = EDVR1.EFM,
EDVR.PRI_DEP = EDVR1.PRI_DEP, EDVR.SEC_DEP = EDVR1.SEC_DEP, EDVR.DOS =
EDVR1.DOS, EDVR.ADSD = EDVR1.ADSD, EDVR.BR_CL = EDVR1.BR_CL, EDVR.EAOS
= EDVR1.EAOS, EDVR.SCOL_EXT = EDVR1.SCOL_EXT, EDVR.OTH_EXT =
EDVR1.OTH_EXT, EDVR.OPEX = EDVR1.OPEX, EDVR.PRD = EDVR1.PRD, EDVR.SDCD
= EDVR1.SDCD, EDVR.DATE_RECD = EDVR1.DATE_RECD, EDVR.ACCT_CAT =
EDVR1.ACCT_CAT, EDVR.EDA_EDL = EDVR1.EDA_EDL, EDVR.PG_PL_AUTH =
EDVR1.PG_PL_AUTH, EDVR.[SECTION] = EDVR1.SECTION, EDVR.DTY_STA_DT =
EDVR1.DTY_STA_DT, EDVR.UIC_TO_FRM = EDVR1.UIC_TO_FRM, EDVR.V_RMKS1 =
EDVR1.V_RMKS1, EDVR.V_RMKS2 = EDVR1.V_RMKS2, EDVR.V_RMKS3 =
EDVR1.V_RMKS3, EDVR.V_RMKS4 = EDVR1.V_RMKS4, EDVR.V_RMKS5 =
EDVR1.V_RMKS5, EDVR.A_RATE_CD = EDVR1.A_RATE_CD, EDVR.S_S = EDVR1.S_S,
EDVR.CMP = EDVR1.CMP, EDVR.MCA = EDVR1.MCA, EDVR.POB_START =
EDVR1.POB_START, EDVR.POB_STOP = EDVR1.POB_STOP, EDVR.POB_CAT3 =
EDVR1.POB_CAT3, EDVR.COB = EDVR1.COB, EDVR.TNEC = EDVR1.TNEC, EDVR.UIC
= EDVR1.UIC, EDVR.D_RATE_CD = EDVR1.D_RATE_CD, EDVR.A_FILLER1 =
EDVR1.A_FILLER1, EDVR.ARC = EDVR1.ARC, EDVR.ACT_SHT_TI =
EDVR1.ACT_SHT_TI, EDVR.QTNEC = EDVR1.QTNEC, EDVR.QNNEC = EDVR1.QNNEC,
EDVR.A_FILLER2 = EDVR1.A_FILLER2, EDVR.CW_UIC = EDVR1.CW_UIC,
EDVR.CW_SECT = EDVR1.CW_SECT, EDVR.CW_PART = EDVR1.CW_PART,
EDVR.A_FILLER3 = EDVR1.A_FILLER3, EDVR.TYPE_INVST = EDVR1.TYPE_INVST,
EDVR.INVEST_DT = EDVR1.INVEST_DT, EDVR.CLEAR_ELIG = EDVR1.CLEAR_ELIG,
EDVR.CLEAR_AUTH = EDVR1.CLEAR_AUTH, EDVR.GRANTED_DT =
EDVR1.GRANTED_DT, EDVR.INVOL_EXTS = EDVR1.INVOL_EXTS, EDVR.PEBD =
EDVR1.PEBD, EDVR.TIR = EDVR1.TIR, EDVR.ADV_EFF_DT = EDVR1.ADV_EFF_DT,
EDVR.FORMAN_STA = EDVR1.FORMAN_STA, EDVR.FORMAN_DT = EDVR1.FORMAN_DT,
EDVR.MIL_RANK = EDVR1.MIL_RANK, EDVR.WORK_CTR = EDVR1.WORK_CTR,
EDVR.STATUS = EDVR1.STATUS, EDVR.MOD_DATE = EDVR1.MOD_DATE,
EDVR.MODIFY = EDVR1.MODIFY, EDVR.FLAG = EDVR1.FLAG, EDVR.SSN =
EDVR1.SSN1;

Step 6: Delete any records in EDVR that are not in EDVR1

DELETE EDVR.*, Exists (SELECT EDVR1.SSN1 FROM EDVR1
WHERE EDVR.SSN = EDVR1.SSN1) AS Expr2, EDVR.ACCT_CAT
FROM EDVR
WHERE (((Exists (SELECT EDVR1.SSN1 FROM EDVR1
WHERE EDVR.SSN = EDVR1.SSN1))=False) AND ((EDVR.ACCT_CAT)<>"pg"));

Step 7: Delete all records from EDVR1 (intermediate table)

DELETE EDVR1.*
FROM EDVR1

Step 8: Delete EDVR2 (Accomplished in Code)





What I am wondering is, is this the correct way to accomplish these
tasks? If not I would appreciate help on the subject. If there is a
more efficient way to accomplish this, then that would be great help
to.

This is a MDB and not a project.


Sincerely,
Michael Martin
Click to expand...
 
M

martinmike2

well, the biggest problem we are having is that for some reason
apparantly random people have their unique identifier changed after
the update. This causes immense havoc throughout the system as the
indentifier is the key in many of the tables. You can imagine the
problems you would run into when the wrong social security number is
shown for an individual; not to mention dependents, certifications
held, etc.

Now, if the person is already in the system (IE. current employee)
then their indentifier should not change, as their SSN is already
stored in the SSN table and a number given to it.
 
G

Guest

Are you deleting SSNs from the SSN table? That might
cause problems in your system. Apart from that, it still
looks ok to me. Make a copy of all the tables, then step
through and compare each step to see where the changes
are coming from.

(david)
 
G

Guest

martinmike2 said:
We delete the SSN from the SSNs table only when the person leaves.
Click to expand...

Then it is possible that you might under some circumstances re-use
the auto-number from the SSN table? That would give someone the
wrong SSN.

(david)
 
M

martinmike2

well, we have discussed this problem and have decided to change the
way the system works. We are going to be setting aside a block of
numbers ( 1 - 1000 ) and having the code run through the recordset
until it finds the first record without a value in the SSN field and
placing the SSN in that record. We will never any more that 1000
personnell at this location, so its not a big problem. Now I just
need to devise a way for the code to actually do this, lol.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update querey issue 2

Top