Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update with subquery (Access 2000)

    OK, I hope I could do this with a subquery. Before, I was just using 2 (sometimes 3) to do this, but I am sure there is a better way. I want to basically update the quantity on the product table with the products and its components ordered from a certain invoice. Basically, the quantity in the OrderedProduct table is referred to As Qtyordered and in the table OrderedProductsMixedComponents, it is reffered to as QtyNeeded.

    I am wandering how I could get the value in the subquery to represent the Qtyorderd in the first line (last word) in the SQL String

    Actually, to be more specific. How I was doing to before was I had a query which found the first record in the invoice. Then I used another query to update the product quantity. Finally, if the product had components, then I would fire another query. Therefore I had nested queries, but they would also only update one record. This would loop though the entire invoice. Does doing it this way make that much of difference in terms of performance?

    Here is the query

    SQLUpdateProducts = "UPDATE Products SET QuantityOnHand = QuantityOnHand - " & QtyOrdered _
    & " WHERE (SELECT OrderedProducts.QtyOrdered As QtyOrdered, OrderedProductsMixedComponents.QtyNeeded As QtyOrdered " _
    & " FROM OrderedProducts, OrderedProductsMixedComponents WHERE Products.ProductID = Orderedproducts.ProductID AND Products.ProductID = OrderedproductsMixedComponents.ProductID" _
    & " AND InvoiceID = " & InvoiceID & " Products.IsMixed = false)"

    Thanks again [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update with subquery (Access 2000)

    I'm sorry, I find it hard to visualize what is going here. Can you post some information about the structure of the tables (I know we've been discussing this before, but I don't know the present situation), or perhaps attach a stripped down copy of your database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with subquery (Access 2000)

    Hi Hans.

    The database is still fairly big, even when I remove everything, Currently about 3 megs. Therefore, I will just skip sending the database. I do appreciate the offer though.
    I do have a question in terms of performance. I wil try to give a clearer idea of what I have. Basically, I have a recordset, which finds my first record (based on my criteria). Then I have an update statement which updates the quantity in the products table. (the product which was found in the first recordset.) Since I have only one product, I only update one record.

    For my mixed Components, there is just another recordset and update statement. So basically a nested loop. ( this does not happen very often, mabye 5 % of the time)

    In terms of performance, is it bad to fire an update for just one record and loop to the next record in a recordset. On average, this would loop about 20 times per invoice. I would have at most 7 users on the system at one time. (not all entering invoices, but doing other tasks)

    Thanks a bunch.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with subquery (Access 2000)

    OK how about this. I have 3 different scenerios I have to take care of (Products, Mixed Products and Blocks), so I will handle each of them seperatly with there own update statement. Therefore, lets say I want to update the group of records based on a select statement. In This Case, Regular Products:
    "UPDATE Products SET Products.QuantityOnHand = Products.QuantityOnHand - " & QtyOrdered)_
    & " WHERE (SELECT OrderedProducts.QtyOrdered As QtyOrdered " _
    & " FROM OrderedProducts WHERE Products.ProductID = Orderedproducts.ProductID AND Products.ProductID = OrderedproductsMixedComponents.ProductID" _
    & " AND InvoiceID = " & InvoiceID & " Products.IsMixed = false)"

    I basically want the value from the select statement to go into QtyOrdered. Can I just put the Select Statement where the QtyOrdered text is? This would solve my problem is I could do it this way. The statement above is just a rought copy

    Thanks [img]/forums/images/smilies/smile.gif[/img]

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with subquery (Access 2000)

    OK, here is a slightly different way, and I am sure this would work, but I am not sure why it isn't. I am have the first query which gives me the records I would like to update(Records from the invoice). My second query is to use the results from the first query, but everytime I run it, I get asked for parameter values (where the query in referenced)

    First Query: Nameqry_GetProductsGettingShippedForFaxIn)
    SELECT OrderedProducts.QtyOrdered AS QtyOrdered, OrderedProducts.ProductID AS ProductID
    FROM OrderedProducts
    WHERE (((OrderedProducts.IsMixed)=False) AND ((OrderedProducts.InvoiceID)=[Forms]![FRM_FaxInCustomer].[InvoiceID])) AND ((OrderedProducts.ProductStatus) In ('I','F','R','N'));

    Second Query:
    UPDATE Products SET Products.QtyOnHand = QtyOnHand - ([qry_GetProductsGettingShippedForFaxIn].[QtyOrdered])
    WHERE ((Products.ProductID) = ([qry_GetProductsGettingShippedForFaxIn].[ProductID]));

    How can I get the results from the first query to work in the second one? I have it working fine in other instances, but records are only getting returned, not updated.

    Thanks

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with subquery (Access 2000)

    ACtaully I got it. I will just do the way I have always done it, (which actaully turns out to be similar to what I wanted to do anyway. Just had a huge laps in focus.

    Time for a slurpee

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update with subquery (Access 2000)

    Apparently, you were able to continue the conversation on your own while I was away from the Lounge <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    I take it you've solved the problem now?

  8. #8
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update with subquery (Access 2000)

    Hi Hans, everything is fixed now [img]/forums/images/smilies/smile.gif[/img]

    Thanks

Posting Permissions

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