Update table from another table

[Sniper-BoOyA-]

Morning,

I have 2 tables;

Compression and Compression_Import.

Table Compression is currently used to enter the load applied to the sample followed by a query that calculates the 'compression strength' using the entered load and the surface of the sample.

Table: Compression
.Labnr .,.... Load
13-142
13-142
13-142
13-142
13-142
13-142

What i would like to do is to import both the 'load' and 'compression strenght' from the machine.

So i have managed to do an export on the machine (to Excel) and then made an import function which imports the content of the Excel file and puts it in a table called Compression_Import.

Table: Compression_Import
.Labnr .,.... Load........Compression strength
13-142.....11372..................64.3
13-142.....11376..................64.9
13-142.....11379..................65.3
13-142.....11343..................61.3
13-142.....11372..................64.3
13-142.....11371..................64.3

What i'd like to do is to update Compression.load with the value of Compression_import.load.

I have tried several things like

SQL Statement:

UPDATE Compression_import INNER JOIN Compression ON Compression_Import.Labnr = Compression.labnr SET Compression.load = Compression_Import.[load];

But all it only seems to get the value of the last labnr from Compression Import and updates everything with it.

Result:

13-142.....11371
13-142.....11371
13-142.....11371
13-142.....11371
13-142.....11371
13-142.....11371

Is there a way of doing this using VBA? Or am i doing something wrong in the SQL statement?

I have been trying to get this to work for a couple of hours, but after checking the internet for the last hour or so i can not get it to work at all..

Thanks in advance.

Regards,

Michael

 

[namliam]

What is your key here? It seems to be LAB NR which seems to be non-unique, this inside databases is always going to cause issues...

Are you not getting the combined information from the machine?

 

[Sniper-BoOyA-]

The table Compression's key is ID which is an autonumbering field.

The user enters data using a form called LABDATA and as soon as all necesarry information is entered, a query will run to add given labnr to table Compression 6 times. Why? Because for every labnr the technician as to perform 6 compression tests. And thats the reason why labnr cant be unique (primary key).

The information we get out of the machine is not complete but we do need both the 'load' and 'compression strenght'.

At this point, the people in the lab enter the load by hand (into the database) and the compression strength is calculated using a query.

But to automate this proces we would like to import both the load and the compression strength from the machine.

I got it to import it into a database (temporary table) but i would like to update the Compression table with the data from table Compression_Import.

 

[Sniper-BoOyA-]

My apologies. I just found out i dont really need to copy data to the table. I might aswell use the imported table instead of the original 'Compression' table.

And as soon as ive calculated everything using queries, link the final query with the table with Labnr as primary key and then it should work.

Anyways, thanks for responding on such a short notice.

You can consider this post as 'solved'.