locked
SQL Update Command RRS feed

  • Question

  • Hey I'm trying to write a SQL command in MS Access to update from one sheet to another with parameters. What I am doing is updating product availability from one sheet to the master list from all vendors. Basically need to set something to update the master list if the vendor list says it is in stock to say that or if not to change it to out of stock. Is there a way to do this in one command?

    Tuesday, February 4, 2014 6:59 AM

Answers

  • Depending on what you get from the vendor:

    * If you get Boolean True / False for Avail / Not Avail:

    SET [YourMaster].StockStatus = IIf[VendorProductStatus].[StockAvail], 1, 2)

     

    * If you get a number 0 (Not Avail.) or >0 (Avail.)

    SET [YourMaster].StockStatus = IIf([VendorProductStatus].[StockNo] > 0, 1, 2)

     

    * If you get some text value like "Yes" and "No"

    SET [YourMaster].StockStatus = IIf[VendorProductStatus].[StockAvail] = "Yes", 1, 2)

    (this defaults to StockStatus = 2 if Vendor StockAvail is different from "Yes")

     


    Van Dinh

    • Marked as answer by Drathore Wednesday, February 5, 2014 5:45 PM
    Wednesday, February 5, 2014 4:49 AM

All replies

  • All this talk of sheets and copying data makes me nervous... is this part of an import/organization attempt, or is this part of a business rule that needs to be satisfied?  Based on your last sentence, it sounds like a business rule, and it sounds like it's not normalized, because it sounds like you're storing this data twice instead of storing it once and looking it up when required.

    Having to run update queries when other data is changed is also a bad sign for non-normalized data.

    Can you explain your data relationship a little more?

    Cheers


    Jack D. Leach (Access MVP)
    Dymeng Services (home | blog)
    UtterAccess Wiki (hundreds of articles and functions)

    Tuesday, February 4, 2014 9:40 AM
  • Like Jack, my antennae start twitching when I hear the word 'sheet'.   It suggests a spreadsheet model rather than a relational database model.  At its simplest, where each product is supplied by one vendor only, the model in a database would in broad outline comprise the following tables:

    Products
    ....ProductID (PK)
    ....Product
    ....VendorID  (FK)
    ....UnitPrice
    ....InStock

    Vendors
    ....VendorID (PK)
    ....Vendor
    .....etc

    Where a product can be supplied by one or more vendors the binary relationship type between products and vendors would be modelled by a further table, so the tables would be:

    Products
    ....ProductID (PK)
    ....Product

    Vendors
    ....VendorID (PK)
    ....Vendor
    .....etc

    and to model the relationship type:

    ProductVendors
    ....ProductID (FK)
    ....VendorID (FK)
    ....UnitPrice
    ....InStock

    In the latter scenario both UnitPrice and InStock are attributes of the relationship type.  In the first scenario the InStock value (a Boolean TRUE or FALSE) is stored once only per product, in the second scenario once only per product/vendor, so there is never any need to update one table on the basis of an update in another.

    Ken Sheridan, Stafford, England

    Tuesday, February 4, 2014 12:43 PM
  • Sorry should have been more clear I work on the backend of a company website where we deal with drop ship vendors so product never is handled by us, so they send us spreadsheets with the availability of there products, I am trying to simplify updating the products on our site from those speadsheets as each vendor sends us data in different formats.

    I have been able to use a SQL update command to update the product list but that requires me to split each list from vendors into not active and active before running the update command. Truthfully the easiest way would be to put an IF-THEN statement into the sql command but I have not found a way to make that work.

    Wednesday, February 5, 2014 2:10 AM
  • >> the easiest way would be to pan an IF-THEN statement into the SQL <<

    Are you familiar with Iif()?  It's an Immediate If, as it's called:

    =IIF(SomeExpresion, TruePart, FalsePart)

    It can be helpful in some cases.

    Also, you might try to do some of this in VBA, where you can construct better logic, and dynamically build the SQL strings to execute.  I tend to find that when importing, it's usually easier to clean up the data with a combination of VBA and SQL, then import everything into the final tables.

    Cheers,
    -jack


    Jack D. Leach (Access MVP)
    Dymeng Services (home | blog)
    UtterAccess Wiki (hundreds of articles and functions)

    Wednesday, February 5, 2014 2:20 AM
  • Presumably, you have some Field ProductID for each product, then it should be a simple UPDATE SQL something like:

    UPDATE [YourMaster] 
      INNER JOIN [VendorProductStatus]
      ON [YourMaster].ProductID = [VendorProductStatus].ProductID
    SET [YourMaster].StockStatus = [VendorProductStatus].[StockStatus]

     

    assuming that each ProductID in [YourMaster] is unique across all products from all vendors.  If you have the same ProductID that can be supplied by different Vendors, then you need to use additional conditions, either in the ON clause or in the WHERE clause.

     


    Van Dinh

    Wednesday, February 5, 2014 3:16 AM
  • Each ProductID is unique but each vendor sends me the status of product in a different format so some will just say available or not some will give me the number available. The other problem is on my side in the table that is loaded into the backend of our site I need to have it marked as either a 1 for active or 2 for inactive. It's massively unorganized as you can see. 

    Thank you Jack I may try that

    Wednesday, February 5, 2014 3:22 AM
  • Depending on what you get from the vendor:

    * If you get Boolean True / False for Avail / Not Avail:

    SET [YourMaster].StockStatus = IIf[VendorProductStatus].[StockAvail], 1, 2)

     

    * If you get a number 0 (Not Avail.) or >0 (Avail.)

    SET [YourMaster].StockStatus = IIf([VendorProductStatus].[StockNo] > 0, 1, 2)

     

    * If you get some text value like "Yes" and "No"

    SET [YourMaster].StockStatus = IIf[VendorProductStatus].[StockAvail] = "Yes", 1, 2)

    (this defaults to StockStatus = 2 if Vendor StockAvail is different from "Yes")

     


    Van Dinh

    • Marked as answer by Drathore Wednesday, February 5, 2014 5:45 PM
    Wednesday, February 5, 2014 4:49 AM