Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (Access 2000)

    I want to update a row in the table with the result from a query but clearly the update query is not so easy. Could you help me with the update query ? I am trying to update the field items4 from the table products to be equal to the field 0 of the query qrySuborder.Access does not allow me to do that.I am attaching an example
    Attached Files Attached Files

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

    Re: Update query (Access 2000)

    A crosstab query is not updatable, hence an update query based on a crosstab query doesn't work.

    Apart from that, it is not clear what should happen. qrySuborder contains two records for product bbb, so there are two values for the field "0". Which of these should be used?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Thank you Hans. Actually it is the difference betwen these 2 values in the query qrySuborder that i am interested.I am sending anew a new example to illustrate what i mean. For example the product aaa from the first column is 100. This product in the second coloumn is 20 + 30 = 50.The differene is 100 - 50 = 50. I want to update the field items4 in the table Product to read items4 = 5.
    This query is available in my database so i thought i might use it to update my table products. If it is not possible, then i wil look for other possibilites.
    Attached Files Attached Files

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

    Re: Update query (Access 2000)

    Why would you want to update Items4 to 5 if the difference is 50?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Thank you for your ineterest. Items4 in the table products represents the quanitty available in the warehouse. The first coloumn in the query represents the quantities which we have delievered in the warehouse,(100) the second coloumn represnets the quantities which we have sold.(50) This query works excellent and is the basis of a report with 2 coloumns, made with your help and works marvelous. So the difference between the imported and exported items( 100 - 50) should be equal to the items in the warehouse. I want to update from time to time these items in the warehosue, which should be equal to the difference between the delivered and sold items.

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

    Re: Update query (Access 2000)

    You didn't really answer my question. But see if the attached does what you want.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Oh Yes ! And nice does it what i want. Thank you very much !

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Dear Hans

    Your good advices were taken into bad hands and i have ended with a complete mess. it is my fault because you have given excellent answers but my questions were clealry not well formulated.I think it is better to send to you the concrete details of my problem with the example.So in the example i want to sum up all the quantities for the product aaa, having aidit = true and suborder = True.There can be only one audit and many suborders.
    In my example :
    audit 100
    -------------------------
    suborders 20
    1
    -----------------------------
    total 121
    --------------------------------------------------
    The quantities where suborder = False, are the output quantities:
    30
    2
    3

    total 35
    I need to substract 121 - 35 = 86
    And with this figure i need to make items4 = 86. Could you do something about my problem?
    Attached Files Attached Files

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

    Re: Update query (Access 2000)

    There are no records for which Audit = True and Suborder = True. Do you mean that you want to look at records where Audit = True or Suborder = True?

    The crosstab query in your database has a quite different purpose, it cannot be used for what you want to do here.

  10. #10
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Thank you ! At least i will know that the crosstab query cannot be used here. I am sending the new attachement. In the input query both audit and suborder are True.In the output query r they are False. I need to substract the sum for each product, in my case product aaa and with this sum to update the field items4 of the table products.
    Attached Files Attached Files

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

    Re: Update query (Access 2000)

    qryOutput should be a totals query, just like qryInput. Otherwise, you get multiple records for the same product.

    You can create a query that combines qryInput and qryOutput, joined on ProductID, to compute the difference.
    Then create an update query based in the products table that uses this query.
    See attached version.
    Attached Files Attached Files

  12. #12
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Dear Hans

    You have solved all my problems with one stroke! Needles to say that what you suggetsed was perfect .
    i wish you a Mery Christmas and please accept my best wishes

    Freelance

Posting Permissions

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