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

    Update problem (Access 2000)

    I have the following query:
    UPDATE products1 SET products1.branch0 = [products1].[items0]/[products1].[pack];

    It divides the number of the items to the number of the packs and updates the number of the packs in the stock.For example product A has 24 items and the pack is 12 .The result will be 24/12 = 2 cartons
    Also if we have 48 items of the same product, the result will be 4..
    However, if the items are less than 1 pack, for example 11 items, the result is 1 carton, while i want to be 0 cartons, since the pack is already opened and actually it is not one pack, even though it contains 11 items.
    Can you help me ?

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

    Re: Update problem (Access 2000)

    Does this do what you want?
    <code>
    UPDATE products1 SET products1.branch0 = [products1].[items0][products1].[pack];
    </code>
    The operator is integer division.

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

    Re: Update problem (Access 2000)

    Yes, that is what i want ! Huge thanks !

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

    Re: Update problem (Access 2000)

    Dear Hans, your code worked excellent on a trial table containing several products. But obviously my real table is corrupted and wrongly handled, because the query gave me "Microsoft Access cant update all the records in the update query, due to a type conversion failure,key violations and validation rule vuilations". Otherwise that is exactly what i want to achieve.Is there any other method easier for me to apply ?

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

    Re: Update problem (Access 2000)

    The error message probably means that some of the values are not suitable for calculating, for example you cannot divide by 0. So try adding a where-condition:
    <code>
    UPDATE products1 SET branch0 = [items0][pack] WHERE pack <> 0</code>

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

    Re: Update problem (Access 2000)

    YES ! YES ! It does the trick and i have it, so important for me ! Thank you !

Posting Permissions

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