Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulating data (2000)

    Hi,
    I have a well established database that I need to alter data in on a large scale.
    Looking for some advice really as I am currently in the pre-empting stage.
    The database has a table that has specific records relating to specific areas.
    So for Instance, East & West.

    When the user enters the database they are prompted for the area that they belong to and the database opens filtering those records. Any new records are automatically assigned to that area using an openarg which assigns an AreaID.

    My query is, the areas are now changing based on unique references for each case. So they will now have different areas.
    The old records also have unique references, but they were still encompassed with East & West.

    Adding new records isn't a problem, but is there a quick way of going through the old records (say with a query) a changing the AreaID in the actual table as opposed to manually going through the table and altering every record. There are 900 records.

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

    Re: Manipulating data (2000)

    If you can define an unambiguous "rule" that specifies how the AreaID should be changed, you can use an update query.

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

    Re: Manipulating data (2000)

    If there is a unique set of information that allows you to determine the new area for most or all of the records, you should be able to create an update query that would do the trick, perhaps with manual editing of a few records. I would suggest you add a new field that you create the new area information in. You should find the IIF(Condition, True Value, False Value) construct useful in calculating the new area info.
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating data (2000)

    Thanks for this, its a great help.
    I do indeed have an identifying reference unique to each record which details the relevant area within the reference number.
    I've got about 6 weeks to resolve this before it needs to be changed, so I shall start fiddling now and go from there.
    Thanks again!

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating data (2000)

    Nigel,
    You could also look at creating a temporary table, just for changing your info.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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