Set value based on result

[Heidestrand]

Hey community (and a happy new year to all of you),

I need your help for a simple problem: I want to calculate the balance for each order number I have in my table. If the result is = 1 it should set the value = true in column "won" (it's a yes/no column).

So I've coded something like this:

UPDATE tblFinalOrder
SET won = true
WHERE '1' EXISTS (SELECT Sum(tblFinalOrder.System_AEMenge) AS Sum, tblFinalOrder.SAPNr
FROM tblFinalOrder
GROUP BY tblFinalOrder.SAPNr);

But it's not completely working, it gives me a syntax error.

Do you know where the problem is?

 

[sneuberg]

I don't understand what you are trying to accomplish. What confuses me is that you are setting a field in table where the criteria is an aggregate form the same table. What's the key in the the tblFinalOrder? Please post some data from the tblFinalOrder table with the results you expect. Suggest filling in won manually and then give us a screen shot of the table in data sheet view.

 

[Heidestrand]

So first I want to calculate the balance for each order number in column SAPNr. With the code from the SELECT part I get this result:

Then for every number that has a "1" I want to set the value in my yes/no column "won" to true in my table tblFinalOrder.

Do you understand what I mean?

 

[sneuberg]

No I still don't understand. Please post a screen shot of the table tblFinalOrder; the raw data from which this subquery was derived.

 

[Heidestrand]

This is the raw data for the sub query:

Then I apply my code

SELECT Sum(tblFinalOrder.System_AEMenge) AS Sum, tblFinalOrder.SAPNr
FROM tblFinalOrder
GROUP BY tblFinalOrder.SAPNr

.. to get the results as shown in post 3. So I aggregated the numbers for every SAP number.

And now I only want to set the value to true for every SAP number that has a 1 in my results.

 

[arnelgp]

UPDATE tblFinalOrder
SET won = true WHERE tblFinalOrder.SAPNr IN 
  (
      SELECT SAPNr FROM 
      (
          SELECT Sum(tblFinalOrder.System_AEMenge) AS Sum, tblFinalOrder.SAPNr
          FROM tblFinalOrder
          GROUP BY tblFinalOrder.SAPNr HAVING Sum(tblFinalOrder.System_AEMenge)=1)
       )
   );

 

[sneuberg]

I think arnelg nailed it. Just need to take the extra parenthesis out after the 1 like:

UPDATE tblFinalOrder SET won = true
WHERE tblFinalOrder.SAPNr IN 
  (
      SELECT SAPNr FROM 
      (
          SELECT Sum(tblFinalOrder.System_AEMenge) AS Sum, tblFinalOrder.SAPNr
          FROM tblFinalOrder
          GROUP BY tblFinalOrder.SAPNr HAVING Sum(tblFinalOrder.System_AEMenge)=1
       )
   );

 

[Heidestrand]

Thank you a lot, arnelg, you're the master! =)

Every time I read something written by you it's like.. ok.. I was on the right track but there's always something I wouldn't come up with You're so helpful

Also thanks to sneuberg for helping as well

 

[arnelgp]

have you tried it yet? thank you also and to mr.sneuberg (good eyesight!).

 

[sneuberg]

have you tried it yet? thank you also and to mr.sneuberg (good eyesight!).

My eyesight isn't so hot. I tried it. That's how I found the extra parenthesis.

 

[Heidestrand]

have you tried it yet? thank you also and to mr.sneuberg (good eyesight!).

Yes I did, it works how I wanted it I also fixed the small issue with the extra parenthesis.
It's so cool that you just throw in some code and it works.. I want to be as good as you