Can someone help me with this pls?

[netbytes]

Hi,

I just want to ask how to consolidate these 3 queries with different SET into a single update query?

1st query - UpdateMAP

UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno=Teledynamics.[Item Number] SET Teledynamics.map = IIf([Teledynamics].[MAP]>0,[QualityMatters].[productprice],[Teledynamics].[MAP]);

2nd query - UpdatePrices

UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno=Teledynamics.[Item Number] SET QualityMatters.listprice = [listprice]+([vendor_price]-[Price]), QualityMatters.productprice = [productprice]+([vendor_price]-[Price]);

3rd query - UpdateStocks

UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number]=QualityMatters.vendor_partno SET QualityMatters.customfield1 = IIf(Teledynamics.[Quantity Available]=0,"OUT OF STOCK",IIf(Teledynamics.[Quantity Available]>10,"IN STOCK",IIf(Teledynamics.[Quantity Available]<=10,"LOW STOCK","Discontinued")));

I just want to know what are the codes will I use for them.


Thank you,

 

[FoFa]

The first two can be combined because you are updating the same table.
UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno=Teledynamics.[Item Number] SET Teledynamics.map = IIf([Teledynamics].[MAP]>0,[QualityMatters].[productprice],[Teledynamics].[MAP]), QualityMatters.listprice =[listprice]+([vendor_price]-[Price]), QualityMatters.productprice = [productprice]+([vendor_price]-[Price]);

 

[MSAccessRookie]

The first two can be combined because you are updating the same table.
UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno=Teledynamics.[Item Number] SET Teledynamics.map = IIf([Teledynamics].[MAP]>0,[QualityMatters].[productprice],[Teledynamics].[MAP]), QualityMatters.listprice =[listprice]+([vendor_price]-[Price]), QualityMatters.productprice = [productprice]+([vendor_price]-[Price]);

In addition, if the third query parameters were reversed, I believe that it could be added as well. Look at the modified query and see if it works for you.

UPDATE [COLOR=red][B]QualityMatters INNER JOIN Teledynamics[/B][/COLOR] 
ON [B][COLOR=red]QualityMatters.vendor_partno=Teledynamics.[Item  Number][/COLOR][/B] 
[B]SET QualityMatters.customfield1[/B] = 
  IIf(Teledynamics.[Quantity Available]=0,"OUT OF STOCK",
    IIf(Teledynamics.[Quantity Available]>10,"IN STOCK",
      IIf(Teledynamics.[Quantity Available]<=10,"LOW STOCK", 
        "Discontinued")));

If this code works, then you can add the third query as well to make it something like this (AIR CODE and untested due to lack of database, but it should be close)

UPDATE QualityMatters INNER JOIN Teledynamics 
ON QualityMatters.vendor_partno=Teledynamics.[Item Number] 
SET Teledynamics.map = IIf([Teledynamics].[MAP]>0,[QualityMatters].[productprice],[Teledynamics].[MAP]), 
QualityMatters.listprice =[listprice]+([vendor_price]-[Price]), 
QualityMatters.productprice = [productprice]+([vendor_price]-[Price]), [B]QualityMatters.customfield1[/B] = 
  IIf(Teledynamics.[Quantity Available]=0,"OUT OF STOCK",
    IIf(Teledynamics.[Quantity Available]>10,"IN STOCK",
      IIf(Teledynamics.[Quantity Available]<=10,"LOW STOCK", 
        "Discontinued")));