Update querey issue

M

martinmike2

Hello:

I am running an update query to update a table with data from a higher
authority. My problem is that when I run the query it deletes any
newer data in my main table.

for example: I have a table called EDVR. This table gets updated
once a month by our higher authority and we run the update query to
reflect the changes. While the higher authority is keeping their
master copy of this table, we are making changes to ours. The EDVR
table contains data regarding our personnel's pay, position in the
company, specialties etc. etc. We get new personnel regularly that
are not reflected in the "Current" push from the higher authority, so
we have to add them to our EDVR table. When the next month rolls
around and we get the new push and run the update query, any personnel
not in the new push are deleted from our EDVR table. This is not good
as it results in having to re-input the data. This can take upwards
of a day, depending on the amount of data lost.

Here is the query:

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;


Am I missing something? Is it as simple as a WHERE clause?

Any help would be appreciated
 
T

Tom van Stiphout

On Mon, 26 Jan 2009 05:07:19 -0800 (PST), martinmike2

Update queries cannot delete data, but they can destroy data. They
can't remove existing rows altogether (like delete queries can), but
they can change data in existing rows.
So perhaps you are not telling us the whole story and there is a
delete query involved. Perhaps the import is done using a macro and
that macro runs several queries.

Also I noticed one of the field names is NAME, which is a reserved
word. Change that in the query to [NAME].

-Tom.
Microsoft Access MVP


Hello:

I am running an update query to update a table with data from a higher
authority. My problem is that when I run the query it deletes any
newer data in my main table.

for example: I have a table called EDVR. This table gets updated
once a month by our higher authority and we run the update query to
reflect the changes. While the higher authority is keeping their
master copy of this table, we are making changes to ours. The EDVR
table contains data regarding our personnel's pay, position in the
company, specialties etc. etc. We get new personnel regularly that
are not reflected in the "Current" push from the higher authority, so
we have to add them to our EDVR table. When the next month rolls
around and we get the new push and run the update query, any personnel
not in the new push are deleted from our EDVR table. This is not good
as it results in having to re-input the data. This can take upwards
of a day, depending on the amount of data lost.

Here is the query:

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;


Am I missing something? Is it as simple as a WHERE clause?

Any help would be appreciated
Click to expand...
 
M

martinmike2

Ok, heres the whole process:

1: download new table from higher authority

2: import new table into database (DoCmd.RunCommand acCmdImport)
(imports as EDVR2)

3: remove duplicate entries (INSERT INTO EDVR1 SELECT DISTINCT EDVR2.*
FROM EDVR2;)

4: update social security numbers in SSN table (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));)

5: insert AIMD number (identifier) into EDVR1(temp Table) (UPDATE
EDVR1 INNER JOIN SSNs ON EDVR1.SSN = SSNs.Actual_SSN SET EDVR1.SSN1 =
SSNs.SSN;)

6: add new records to EDVR (INSERT INTO EDVR ... WHERE (((EDVR.SSN) Is
Null));)

7: run the update query

8: delete any old records from the EDVR (this is where i think the
problem is) ( DELETE EDVR.*, Exists (SELECT EDVR1.SSN1 FROM EDVR1
WHERE EDVR.SSN = EDVR1.SSN1) AS Expr1
FROM EDVR
WHERE (((Exists (SELECT EDVR1.SSN1 FROM EDVR1
WHERE EDVR.SSN = EDVR1.SSN1))=False));)

9: delete EDVR2 table

Step 8.....I see what the problem is here, but don't know how to fix
it. We need to delete the old data but we need to kepe the data that
we have added during the last "cycle"
 

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

Am I doing this correctly? 6
table 15
Access front end to Oracle table user load 3
Update query error: "Operation must use an updateable query" 8
manually update field - want to create calc. but probl prev. table 3
Update Access Table from Excel 0
Update query error: "Operation must use an updateable query." 3
Update Query using Excel data as input 8

Top