Search code examples
sqldatabasems-accessdatatable

How to Update a Column in Microsoft Access with Multiplication Based on Boolean Values?

I have a table named 'Uyeler' with the primary key 'TCKimlik.' The tables 'Borc', 'AidatDurum' and 'AylikAidat' have foreign keys based on this 'TCKimlik' value.

enter image description here

In the image above, I want to populate the 'ToplamBorc' column based on the boolean values being 'false' in the other tables below. The values in the columns with the same name in the respective tables should be multiplied, and the result should be placed in the 'ToplamBorc' column.

enter image description here enter image description here

For example, if the 'Ocak' and 'Subat' columns are 'true' and the remaining columns are 'false,' it will write the value of 1000 (resulting from 500 * 2) to the 'ToplamBorc' colmn.

Solution

  • Here is a possible all-SQL solution, using 5 fields - expand as needed. A UNION can have up to 50 SELECT lines.

    Query: AA_UNION

    SELECT UYEID, "Ocak" AS AA_Col, Ocak AS AA_Data FROM AylikAidat
UNION SELECT UYEID, "Subat", Subat FROM AylikAidat
UNION SELECT UYEID, "Mart", Mart FROM AylikAidat
UNION SELECT UYEID, "Nisan", Nisan FROM AylikAidat;

    Query: AD_UNION

    SELECT UYEID, "Ocak" AS AD_Col, Ocak *-1 AS AD_Data FROM AidatDurum
UNION SELECT UYEID, "Subat", Subat*-1 FROM AidatDurum
UNION SELECT UYEID, "Mart", Mart*-1 FROM AidatDurum
UNION SELECT UYEID, "Nisan", Nisan*-1 FROM AidatDurum;

    Query:

    SELECT AA_UNION.UYEID, Sum(AA_Data * AD_Data) AS ToplamBorc
FROM AD_UNION INNER JOIN AA_UNION ON (AD_UNION.AD_Col = AA_UNION.AA_Col) AND (AD_UNION.UYEID = AA_UNION.UYEID)
GROUP BY AA_UNION.UYEID;