CODEWITHSUNDEEP
sqlms-accessms-office
Noobie
Noobie

Reputation: 61

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

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: enter image description here But nothing happens to the Surge Arrester Field internal ID/IDS field, it remains empty: enter image description here The data is there, in the Excel linked table. enter image description here 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.com/watch?v=iIberC3EpnE&t=153s

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

Upvotes: -1

Views: 40

Answers (2)

Gustav
Gustav

Reputation: 55981

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

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112672

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]
FROM 
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 
    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.

Upvotes: 3

Related Questions