Append or update query based on dlookup

[CBR1000f]

Hi all

I'm a user not a programmer and can only sometimes follow VBA.

I have copied some code from this forum to create a temp table using transferspreadsheet.

Now I want to copy a field into its destination table.

I can extract the field using dlookup in a select query (Expr1: DLookUp("[F1]","tbl_opr_temp","[f29]=" & [f29]+4)) but can't convert the select query into an append query.

The primary key field for the destination table is application_id. I added the application_id value to each record in the temp table so I could make an association between the temp and destination tables.

I haven't been able to get put field I want to extract into its destination record.

Can anyone steer me in the right direction? Please?

 

[Isskint]

Hello


It is a simple process, you just need to make your SELECT query into an APPEND query.

• Open your query in design view.
• On the toolbar you will find a button labelled Query Type (12th button on standard menu)
• Select Append qyery
• A pop up box will appear asking for the destination table, select your table name
• A new 'line' will appear above the criteria rows in the editor, labelled Append To; Select the field name in your destination table that each field in the query should be added to (If the query fields are the same as the destination fields these will already be populated)
  • NB Do NOT select autonumber fields, these will automatically be added to the appended records
• Save and run

Have a look at the images attached

 

[John Big Booty]

Are you sure you want an Append Query and not an Update Query?

 

[Isskint]

Good question JB. I assumed it was adding new data but it could be editing existing data. Let us know CBR

 

[John Big Booty]

I only thought Update as he was talking about the Primary Key field. Unless he meant Foreign Key

 

[CBR1000f]

Thanks Isskint. It went just like you said it would but when I tried to run the append query it didn't add the record due to key violations.

Perhaps JBB has spotted my problem. I tried to switch to an Update query, but can't nominate a field in the Update To: line in design view. Do I need to do something else too?

By the way JBB, I followed some of your posts to get the transferspreadsheet function to work - thank you for your help!

 

[John Big Booty]

Are you able to post a copy of your DB ('03 version if pos )?

 

[CBR1000f]

"I haven't been able to get put field I want to extract into its destination record."

Sorry about this rubbish. I haven't been able to put the field extracted from the temp table into its destination record.

 

[CBR1000f]

I think so JBB. It is split into a front and back, so if I post the front, users won't be able to access the data right? Privacy and all that stuff...

 

[CBR1000f]

Maybe I can't attach a copy of the .mdb because I haven't been a member for long enough?

 

[John Big Booty]

Simply replace any sensitive data with dummy data (in a copy of your DB of course), do a compact and repair, put it in a zip file and you will be able to upload that per the instructions here.

 

[John Big Booty]

... and we will need both front and back ends

 

[CBR1000f]

Here you go JBB - I hope you can make sense of it. The query I've been wrestling with is q80_find_recruitment_story_6

 

[CBR1000f]

I didn't move the opr tables to the back end yet so you should have everything you need in one place.

 

[John Big Booty]

Sorry when I Try and open the query I get the following message;

and then when it opens it looks like;

 

[CBR1000f]

Sorry about that. The developer who put it together in the first place built in a lot of structure. It's been painful to get as far as I have. But that's my query! f30 in tbl_opr_temp holds the application_id, which is the primary key for the destination table: tbl_opr.

tbl_opr_temp was a spreadsheet with formatting that has been imported. The text If you were unable to recruit* was a heading, and 4 records below that text is the text I want to put into tbl_opr - hence the dlookup.

Are you stuck because of the security or can you guide me further?

 

[John Big Booty]

OK, sorry I think I've just been being a little obtuse, my apologies

I've got your Query to append the record to the Destination table tbl_opr. I can't save the query and upload it for you as I lack the permissions required to do so (save the query that is), but the SQL looks like;

INSERT INTO tbl_opr ( application_id )
SELECT tbl_opr_temp.f30
FROM tbl_opr_temp
WHERE (((tbl_opr_temp.F1) Like "if you were unable to recruit*"))
WITH OWNERACCESS OPTION;

If you open your query in design view and view the SQL you should be able to simply paste the above straight into that window.

 

[CBR1000f]

Hi again JBB. I'm sorry to say that it didn't work.

It broke at ...it didn't add 1 record to the table due to key violations...

If you can bear with me, open tbl_opr_temp. F1 of record 100 contains the text If you were unable to recruit*. F1 of record 104 contains the text entered in response to that question. It is F1 of record 104 that I want to place into recruitment_story_6 of tbl_opr.

Each time tbl_opr_temp is sucked into the database (estimate about 800 times) the record that is 4 rows below the text If you were unable to recruit* will be different, so the dlookup I had planned would find the relevant text regardless of how many records are included in tbl_opr_temp.

 

[John Big Booty]

Is this the error you are getting?

If so it is because the query has already run once and the application_id already exists. So go to your table and delete the record with application_id 2009

Also your SQL should actually look like;

INSERT INTO tbl_opr ( application_id, recruitment_story_6 )
SELECT tbl_opr_temp.f30, tbl_opr_temp.F1
FROM tbl_opr_temp
WHERE (((tbl_opr_temp.F1) Like "if you were unable to recruit*"))
WITH OWNERACCESS OPTION;

 

[CBR1000f]

Yes that was the error message. But in future the application_id will exist in tbl_opr and additional fields will be added to the record as time passes. Should I be using an update query instead of an append query?

Also, the sql you posted points at the question in tbl_opr_temp, not the answer to the question which is 4 records below. The dlookup points at the answer so I think it should be included somehow. Can you incorporate the dlookup into the sql?

Big love for your help with this so far by the way!