Update table based on condition

[Heidestrand]

Hello community,

I need your help very quick. Actually my problem is pretty easy but I've been trying to find a solution for hours now and can't solve it. That's way I need your help very quick.

I want to update a table called tblFinalOrder, that looks like this:

In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:

My problem is that Access doesn't properly update the table how I want.
I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.

Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.

UPDATE tblFinalOrder AS a 
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr 
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);

I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.

I hope you understand my problem. Actually it's pretty easy but I fail to solve it

I hope you can help me.


Best regards,
Heide

 

[namliam]

why not simply make a cross tab query?

 

[MarkK]

Isn't the problem with the SQL SELECT statment in the where clause? That statement should only select records where the AEMenge = 1, but that statement actually selects ALL the records.

 

[Heidestrand]

Hey, thank both of you for your answers! (and sorry for my late reply)

Actually I should have waited one night and should have taken counsel with my pillow
It was the problem with the SQL statement and the next day I found the solution by myself. Actually so simple but I missed the forest for the trees :banghead: