[maya29]

I want to add records into access table which are new and some updated
I want to add data into table with existing data, without deleting or updating the existing record.
Right now its done like this
First delete data from tbl_request
then insert into tbl_request

i want this to be updating the table with updated data and inserting new data
data looks like

Request id , req_type,description, so on..

Thanks !

[June7]

Use an INSERT SELECT sql action to create new record from an existing record.

Use an UPDATE sql action to change record.

Why do you need to do this?

Are you using bound forms for data entry and edit?

[maya29]

Hi June

My requirement is to update 2 tables in timely fashion so this will be a bat file runs every 2 hrs to get some new and updated data from another db(oracle) .
so right now its done action delete the records in the table and then action insert which give us some issues like when users use the ms access db reports are blanked out or incorrect data shows up, while bat is running.

so I want this to be like search the table and then update or insert .
any clue?
Thanks !

[June7]

So you need to determine if record already exists and if not add it and if it does exist update the data.

This will require two SQL actions.

One will be to insert new records from table that are not already found in other table. This will require a subquery. See http://stackoverflow...e-with-an-extra

The other is an update.

All assumes a unique identifier common to both tables.

[maya29]

INSERT INTO ABREQ ( REQUEST_ID, REQ_TYPE, DESCRIPTION, APPLICATION, REQUEST_TYPE_NAME, BILLABLE, REQ_GROUP, EST_DESIGN, EST_DEV, EST_SYS_TEST, EST_FUNC_DESIGN_DOC, EST_TECH_DESIGN_DOC, EST_SA_OTHER, EST_BA_OTHER, EST_FA_OTHER, EST_DBA_OTHER, EST_FUNC_PEER_REVIEW, EST_TECH_PEER_REVIEW, INTERNAL_CATEGORY, BILLING_DATE, CANCEL_DATE, CLOSE_DATE, REQ_CAT, WARR, WARR_RSN, ORIG_REQ, ACT_ANALYSIS, EST_DESIGN_HOURS, EST_SYS_HOURS, EST_FUNC_DOC_HOURS, EST_FUNC_PEER, EST_FA_OTH_HOURS, EST_DEV_HOURS, EST_TECH_DOC_HOURS, EST_TECH_PEER_HOURS, EST_IMPL_HOURS, REQ_STATUS )
SELECT E.REQUEST_ID, E.REQ_TYPE, E.DESCRIPTION, E.APPLICATION, E.REQUEST_TYPE_NAME, E.BILLABLE, E.REQ_GROUP, E.EST_DESIGN, E.EST_DEV, E.EST_SYS_TEST, E.EST_FUNC_DESIGN_DOC, E.EST_TECH_DESIGN_DOC, E.EST_SA_OTHER, E.EST_BA_OTHER, E.EST_FA_OTHER, E.EST_DBA_OTHER, E.EST_FUNC_PEER_REVIEW, E.EST_TECH_PEER_REVIEW, E.INTERNAL_CATEGORY, E.BILLING_DATE, E.CANCEL_DATE, E.CLOSE_DATE, E.REQ_CAT, E.WARR, E.WARR_RSN, E.ORIG_REQ, E.P_IBM_ACT_ANALYSIS, E.P_IBM_EST_DESIGN_HOURS, E.P_IBM_EST_SYS_HOURS, E.P_IBM_EST_FUNC_DOC_HOURS, E.P_IBM_EST_FUNC_PEER, E.P_IBM_EST_FA_OTH_HOURS, E.P_IBM_EST_DEV_HOURS, E.P_IBM_EST_TECH_DOC_HOURS, E.P_IBM_EST_TECH_PEER_HOURS, E.P_IBM_EST_IMPL_HOURS, E.REQUEST_STATUS
FROM EXTRACT AS E LEFT JOIN ABREQ AS R ON R.REQUEST_ID = E.REQUEST_ID
WHERE (((R.REQUEST_ID) Is Null));

How can I add subquery here.

[June7]

Subquery for what? That sql statement already looks complete. It has a subquery to select the records.

[maya29]

hello June7

I have macro which has 5 queries .
delete (current data), insert(new data),delete (current data), insert(new data).

now my problem is insert i am able to do it as previous post .its working fine.My problem is update and its taking long time and i think its not the best practice how can i check for any change then update the record. for more than 25 columsn in my table
thanks for you r help
Maya 29

[June7]

Not sure what you mean about checking for any change. Maybe a find unmatched query. Do you want to provide sample data and show the UPDATE query?

[maya29]

Table A exists record Z and need to to see any column has data different in regards to tableB.
(table req and table extract has exact same columns.)

25 columns need to be update if any data mismatch is there when we compare with table req and table extract row by row.

UPDATE tbl_req
SET tbl_req.REQ_TYPE = (select REQ_TYPE from EXTRACT), 
tbl_req.DESCRIPTION = (select DESCRIPTION from EXTRACT), 
tbl_req.APPLICATION = (select APPLICATION from EXTRACT), 
tbl_requests.REQUEST_TYPE_NAME = (select REQUEST_TYPE_NAME from EXTRACT), 
tbl_requests.BILLABLE = (select BILLABLE from EXTRACT), 
tbl_requests.REQ_GROUP = (select REQ_GROUP from EXTRACT), 
tbl_requests.EST_DESIGN = (select EST_DESIGN from EXTRACT), 
tbl_requests.EST_DEV = (select EST_DEV from EXTRACT), 
tbl_requests.ST_SYS_TEST = (select EST_SYS_TEST from EXTRACT), 
WHERE ((([EXTRACT].[REQUEST_ID])=[tbl_req].[REQUEST_ID]));

[June7]

I have never seen UPDATE SQL statement with that syntax. Does it work (I doubt it)? Every UPDATE of data between tables I have ever run or seen uses a table join, not a SELECT subquery for each field.

Do you really need to compare or just go ahead and run UPDATE? If data is different it will be changed, if not then the replacement won't matter, it is same data.

What the UPDATE won't identify is if there are IDs in one table that are not in the other, if that is possible. That is where the find unmatched query is needed.

This post has been edited by June7: 29 March 2012 - 06:20 PM

[maya29]

UPDATE tbl_req
SET tbl_req.REQ_TYPE = EXTRACT.REQ_TYPE,
	tbl_req.DESCRIPTION = EXTRACT.DESCRIPTION,
	tbl_req.APPLICATION = EXTRACT.APPLICATION,
	tbl_req.REQUEST_TYPE_NAME = EXTRACT.REQUEST_TYPE_NAME,
	tbl_req.BILLABLE = EXTRACT.BILLABLE,
	tbl_req.REQ_GROUP = EXTRACT.REQ_GROUP,
	tbl_req.EST_DESIGN = EXTRACT.EST_DESIGN,
	tbl_req.EST_DEV = EXTRACT.EST_DEV,
	tbl_req.ST_SYS_TEST = EXTRACT.EST_SYS_TEST,
	WHERE (((tbl_req.REQUEST_ID) Is  NOT Null));

If my extract table has null value how will i update to tbl_req.
thank you
maya29

This post has been edited by maya29: 31 March 2012 - 12:19 PM

[June7]

Query syntax still not correct. Need to join the two tables on the common ID fields. Access Help has guidelines on building query with the query designer.

The Null should pass through in the UPDATE.

Test with a copy of the destination table.

[maya29]

UPDATE tbl_req AS R 
      INNER JOIN EXTRACT AS E ON R.REQUEST_ID = E.REQUEST_ID 
  SET R.REQUEST_ID = E.REQUEST_ID, 
      R.REQ_TYPE = E.REQ_TYPE, 
      R.DESCRIPTION = E.DESCRIPTION, 
      R.APPLICATION = E.APPLICATION, 
      R.REQUEST_TYPE_NAME = E.REQUEST_TYPE_NAME, 
      R.BILLABLE = E.BILLABLE, 
      R.REQ_GROUP = E.REQ_GROUP, 
      R.EST_DESIGN = E.EST_DESIGN, 
      R.EST_DEV = E.EST_DEV
WHERE (((R.REQUEST_ID) Is Not Null));   

I have rewritten like this is this right syntax?
Thanks !
Maya29

This post has been edited by maya29: 02 April 2012 - 11:06 AM

[June7]

Wait, are those ID fields autonumber datatype? If not, and those IDs are representing same record then should work. Although since the IDs are already equivalent, does nothing to set one equal to another. Also, the INNER join should assure that no record has ID that is null so that WHERE is probably extraneous.

Test it on copy of table and see what happens.

This post has been edited by June7: 02 April 2012 - 06:07 PM

[maya29]

hello June

I am getting Error 2950...
message is Operation Must use an updateable query
IDs are representing same record

Any clue?
Maya29