SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Heelp - can't remember how 2 use access!

    I have been away from Access for so long, in the dark recesses of MySql that I've forgotten how to use it!! Short of exporting the data and then reimporting, any Access guru like to help out?

    Basically, it's a conversion thing....

    I have two tables, one like thus:

    ID SubID IDdefininition

    and another pretty much the same

    ID SubID GenericIDdefinition

    the first table actually has loads more fields, but they are irrelevent to this problem.

    I would like to update the IDdefinition in the first table to the GenericID, where the ID AND SubID are the same. I'm buggered if I can remember how to do it.....any help pls???

    (I'll never diss .asp again * )








    * (today)
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Try:

    Code:
    UPDATE Table1
           SET IDefininition = (SELECT GenericIDefininition
                                  FROM TABLE2
                                 WHERE TABLE1.ID = Table2.SubID)
    That MIGHT work for you. You may also have to add another where to the update statement so it maybe like this:
    Code:
    UPDATE Table1
           SET IDefininition = (SELECT GenericIDefininition
                                  FROM TABLE2
                                 WHERE TABLE1.ID = Table2.SubID)
           WHERE ID in (SELECT SubID FROM TABLE2)
    ** Standard disclaimer - This code hasn't been tested at all. Changes may need made to the syntax. Play with those (make copies of the tables so you don't screw up the real data...)

    The other choice you would have is do a select first, then loop through those and use the ID to get the Generic definition....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Cheers Dave

    And in access I'd run this as a data definition query?

    I used to build queries using the "design view" function - I've never actually typed "raw" sql statements into Access (obviously have with MySQL).

    tia

    H
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by TheOriginalH
    Cheers Dave

    And in access I'd run this as a data definition query?

    I used to build queries using the "design view" function - I've never actually typed "raw" sql statements into Access (obviously have with MySQL).

    tia

    H
    Oh, you're doing this in straight access? Hmmm. Have to play with this some.

    What you can do is start in design view, then switch to SQL (on the button) and do it from there. I personally can't stand the design view since it creates some really funked up code.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I always use the SQL view. Not only does the design view create wacky code, it's also really hard to use IMHO - writing straight SQL is much easier
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  6. #6
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree hillsy, the design view does indeed create some wacky code! Also the fact that I haven't quite worked out how to use it. Using SP's in my ASP, I've found it soooooo much easier to to jump into the SQL view!

    If your using Access as your database, I would suggest using SP. It can really help development. Instead of making the database, writing the code with the SQL and then debugging it from there, you can write and debug the SQL before even considering coding! Soooo much easier!


Bookmarks

Posting Permissions

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