Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query?? (2003 All Updates)

    I have a table of products with a field desigbed to show if a product is active or not. ie still available
    I have a query of products no longer available (based on find unmatched query wizard). Both the table and the query have a consistent field called productfullname. I would like to use the query of products no longer available to update the Active field (a yes/no field ) in the table to show that the product is no longer active by setting the Active to N (or similar)
    Help
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Update Query?? (2003 All Updates)

    Don't your products have a ProductID or similar field (number or autonumber)? That's usually more dependable and efficient than using the product name.

    BTW, was the reply to <post:=656,538>post 656,538</post:> helpful?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query?? (2003 All Updates)

    Hans,
    Like most databases I deal with, I have no control over the original data. This is a particularly bad one where the manufacturer has basically lost control of their numbering system eg they allowed 2 digits in their product code for material but now have over 100 materials.
    Using a combination of fields, I can get unique identifiers for the products and thats going OK.
    My problem is that I want to use a query to update the status of one field to make it "No" (or 0 , -1 I can never rmember which one means No!!) For example there were 1200 different products at one stage, and these are now down to about 1,000. I have identified which ones are no longer produced and I want to use this query to set a field in the main table to inactive. Felt like an update query but I couldnt get there.
    Steve
    Yes the Word thing did help, thank you - I did have to recreate the Normal.dot as well but that was no problem
    Thanks again
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Update Query?? (2003 All Updates)

    You can enter 0 or No or False in the 'Update to' line for the Active field - the query engine will interpret each of these as meaning "No".

    Have you tried creating an update query from the Find Unmatched query? If that doesn't work, we'll need to know more about the structure of the table and queries. See <post#=401925>post 401925</post#> for instructions on how to post a stripped down and zipped copy of the database.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query?? (2003 All Updates)

    I am getting "Must use updatable query" - what am I doing wrong.
    I have created an Update Query
    I have the query which finds the unmatched items and the Table I am trying to update linked and am only including the rows where the joined fields match so only the unmatched (ie no longer manufactured items) are selected - this is working fine
    I have one destination field, the Inactive field in the Table which I have set to Update to 0 (or No or False - tried them all. That field type is Yes/No Field.
    I am lost
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Update Query?? (2003 All Updates)

    As I wrote in my previous reply, we'll have to see the database.

Posting Permissions

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