Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update queries (Access 97)

    Hi - can anyone help me with this? I have some VBA code which is first of all creating a table called PricesHold where information from a larger table has a company code "D". At the moment the database then changes that company code to "G" via an update query:

    "dbs.Execute "UPDATE tblPricesHold SET tbl PricesHold.Company = 'G' WHERE (((tbl PricesHold.Company)='D'));"

    What I am now being asked to do is to create 3 companies, G, A and B from the original company 'D' data (essentially taking the information from D, and creating the same information 3 times, but with different company codes. Anybody got any ideas how I can create an update query that does this, or will I have to work around??

    Regards to all. Marion

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

    Re: Update queries (Access 97)

    One solution would be to use a Make Table query and simply set the Company Code as an expression. But Make Table queries don't do expressions very well, so you would have to run an update after the fact to change field names in each table. A better solution would be to Copy the existing table and then paste it with the option to NOT copy the data. Then create an append query which copies all of the records for the "D" table, but uses an expression to set the new company code, and run that query 3 times. (I made an assumption here that you have to have 3 different tables. If you can do it all in the existing table, then all you need is the append query run 3 times with the different company codes.)
    Wendell

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update queries (Access 97)

    Thanks Wendell I managed to get a work around to this. Many thanks

Posting Permissions

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