sql - How can I copy the field (key entries) from one Access Table to another Table? - Stack Overflow

I am new to Access. So I have one Excel linked table in Access "Surge Arrester" which is unmodifiable and can only be modified in Excel. I want to copy the keys from the "Surge Arrester" in the field "ID/IDS" into a new table called "Surge Arrester internal" to the field, also called, "ID/IDS". I really don't understand how I can do this. Nothing I tried worked. I tried this: But nothing happens to the Surge Arrester Field internal ID/IDS field, it remains empty: The data is there, in the Excel linked table. Considerations: -the data in the Surge Arrester is unmodifiable since it's a linked table. -I want to use those keys to create a new table that I can do all sorts of stuff to, like appending other fields, etc. -Chat gpt is dumb and can't tell me why is not working. -I am a newbie in using access or any kind of databases. -I used this tutorial, and everywhere I look I see the same method. ;t=153s

What is happening and why it's not working as intended?

I am new to Access. So I have one Excel linked table in Access "Surge Arrester" which is unmodifiable and can only be modified in Excel. I want to copy the keys from the "Surge Arrester" in the field "ID/IDS" into a new table called "Surge Arrester internal" to the field, also called, "ID/IDS". I really don't understand how I can do this. Nothing I tried worked. I tried this: But nothing happens to the Surge Arrester Field internal ID/IDS field, it remains empty: The data is there, in the Excel linked table. Considerations: -the data in the Surge Arrester is unmodifiable since it's a linked table. -I want to use those keys to create a new table that I can do all sorts of stuff to, like appending other fields, etc. -Chat gpt is dumb and can't tell me why is not working. -I am a newbie in using access or any kind of databases. -I used this tutorial, and everywhere I look I see the same method. https://www.youtube/watch?v=iIberC3EpnE&t=153s

What is happening and why it's not working as intended?

• sql
• ms-access
• ms-office

Share Improve this question Follow asked Mar 2 at 9:33 NoobieNoobie 6588 bronze badges 3

• Do not use an update query, because the local table is empty and update here is meaningless, also the join is in the same way. Simply use an append query, or a make table query without creating a local table. – Shahram Alemzadeh Commented Mar 2 at 10:58
• The thing is I want to constantly update the local table based on the inputs another person will put in the excel table – Noobie Commented Mar 2 at 14:43
• 2 Well, as noted, to get the records into that new table, you need to use a append query. However, you THEN have to deal with future updates - in that case, you need: add new records if they don't exist, and update existing if they do. And then what about deleted records? So, in fact, you have 3 different cases to deal with. – Albert D. Kallal Commented Mar 2 at 18:13

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 4

Well, then you will have to do several things:

1. Update existing records

   UPDATE [Surge Arrester internal] SAI
       INNER JOIN [Surge Arrester] SA
           ON SAI.[ID/IDS] = SA.[ID/IDS]
   SET SAI.Caracteristic = SA.Caracteristica
   

2. Insert missing records

   INSERT INTO [Surge Arrester internal] ([ID/IDS], Caracteristic)
   SELECT [ID/IDS], Caracteristica
   FROM [Surge Arrester] SA
   WHERE SA.[ID/IDS] NOT IN (SELECT [ID/IDS] FROM [Surge Arrester internal])
   

3. Delete extra records (optional)

   DELETE * FROM [Surge Arrester internal]
   WHERE [ID/IDS] NOT IN (SELECT [ID/IDS] FROM [Surge Arrester])
   

Identifiers (table and column names) with spaces and special characters like "/" are not great when writing queries. They require you to put them in square brackets. The underline character "_" is a good replacement for these problematic characters.

First, as you do, link the Excel file.

Next, create a simple select query having the linked Excel table as source. In this query:

• Rename (alias) field names to match those of the destination table
• Specify filtering to exclude unwanted records from the output
• Convert data as needed, so they will match your destination fields

Save the query.

Finally, use this query as source in a combined update and append query, that will insert new records in your destination table and modify those (if any) that exist in the table. Here is how to build such a query:

Update and Append Records with One Query