Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Update Query Help

    I am news to access and was wondering if anyone could help me. I have been trying to run this update query
    UPDATE final_data, request_upc SET final_data.[*QUANTITY*] = [final_data].[*QUANTITY*]+1
    WHERE (([final_data].[*UPC*]=[request_upc].[DVD_UPC]));

    and it worked for a few minutes, but then I get this error
    Complie error. in table-level validation expression.

    Is there something wrong with my db or the query?
    I am trying to get the final_data table to update quantity's matching and counting each upc in the request_upc table. Any help would be great.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    UPDATE final_data SET final_data.[*QUANTITY*] = [final_data].[*QUANTITY*]+1
    FROM final_data inner join request_upc on [final_data].[*UPC*]=[request_upc].[DVD_UPC];


    Since you're at the beginning of your database building career...
    Don't put special characters - like * - in your field names. It will just
    lead to confusion later.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2007
    Posts
    4
    I just tried the query and it gives me a syntax error.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sorry, forgot I was in the Access forum. Access has a goofy syntax for update queries.

    Try it this way...

    UPDATE final_data INNER JOIN request_upc ON final_data.[*upc*] = request_upc.dvd_upc SET final_data.[*quantity*] = [final_data].[*quantity*]+1;
    Inspiration Through Fermentation

  5. #5
    Join Date
    Feb 2007
    Posts
    4
    Well thnx the query works, but only if there is no data in my request_upc table?? any ideas?

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It works only on records in the final_data table that have a matching record in the request_upc table (at least in the test tables/query that I built)
    Inspiration Through Fermentation

  7. #7
    Join Date
    Feb 2007
    Posts
    4
    To give you a little more info. I have a java app that grabs my upc from the request_upc table and get the info I need from the web and then inserts it into my final_data table. What I am trying to acomplish is if I scan two of the same upc int the request upc table I want the query to count them and then update the quantity in the final_data table so I can stop counting my products. it makes work faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •