Thank you!
Welcome aboard. There is no need to store the calculated value and in fact it would be wrong to do so. If you store the result of the case statement, it would only be valid for the brief moment in time when you stored it. As soon as someone updated the database, the calculated value could be wrong. That is why it is incorrect to store values such as this.
Access SQL doesn't include a Case statement but it does include the IIf() function which can be used to return the value you want.
Select IIf(QA > 10, "Instock", IIf(QA < = 10, "Lowstock", IIf(QA = 0, "Outofstock", "Discontinued"))) As Avail, fld2, fld3, fld4, ....
From YourTable
oh! i see. but my problem is this.
I need 1 Update query that will update my two tables.
The tables are QualityMatters and Teledynamics
My project is how to combine these queries into 1 UPDATE Query:
Queries Named as follows:
1. instock query
2. outofstock query
3. lowstock query
4. discontinued query
All running to update my tables but needs to click 1 by 1.
So I need those in one query. I dont know what im going to use. I used a lot of statements but still Im really confused.
Example: I named this query as instock query
UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "IN STOCK"
WHERE (((Teledynamics.[Quantity Available])>10));
Another, lowstock query
UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "LOW STOCK"
WHERE (((Teledynamics.[Quantity Available])>0 And (Teledynamics.[Quantity Available])<10));
another one, outofstock query
UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "OUT OF STOCK"
WHERE (((Teledynamics.[Quantity Available])=0));
and the discontinued query
UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "Discontinued"
WHERE (((Teledynamics.[Quantity Available]) Like 'DISCONTINUED'));
oh! well, i need to use just 1 update query to run and update my two tables. I have 4 queries so I want it to be in only one query which is using UPDATE and SELECT Statements.
Is there a way to combine all of this using 1 UPDATE QUERY ONLY?
Hope you can answer. THANK YOU!
