Results 1 to 5 of 5
  1. #1
    MichaelC
    Guest

    Update Values (A2000)

    I am trying to correct improper numeric identifiers in a field. Here is the plan. I have run a Query that gives me all records that = "DVD" in the format field. I sort them in ascending order by the Date in the release date field. I create a new field called temp number and I want to sequentially number the records beginning with 7001, and continuing by incrementing by one for each record in the query that is already sorted in the proper date order. I run an update query and the number of rows is exponentially higher than in reality...i.e. 32,384 rows as opposed to the actual 690 rows...then the update proceeds but there is no sequential numbering I get 7001 in all records in the query. What am I doing wrong? Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Values (A2000)

    Well, hard to say without seeing your code. It sounds like your SQL is doing a cartesion join, which essentially means it is joining each record from one table with each record in another table. This happens when you don't specify the join criteria.

    As for the numbering, this is very difficult to do via an update query. I'd opt for doing it via code.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    MichaelC
    Guest

    Re: Update Values (A2000)

    Thanks for the response Mark. I am more of a "designer" than a "coder" ... can you recommend any good books on writing code in Access? VB? VBA? Thanks

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Values (A2000)

    For learning VBA, I'd recommend "Beginning Access 97 VBA" by Smith and Sussman (I'm sure other versions are available also).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Update Values (A2000)

    Agreed - it's a WROX book and is how I got serious about VBA.
    Wendell

Posting Permissions

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