Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Advice (WIN 2000 Acc 97)

    MyTable has 20 fields. One is Group#.

    MyGroupTable has 2 fields: GroupNumber and GroupName

    I have added a field to MyTable called GroupName.

    I want to update MyTable.GroupName with the GroupName from MyGroupTable.
    Here is my code - it does not work. What am I doing wrong?

    <hr>UPDATE MyTable LEFT JOIN MyGroupTable ON MyTable.[Group #] = MyGroupTable.[GROUP NUMBER] SET MyTable.GroupName = [MyGroupTable].[Group Name];<hr>

    I appreciate your help.

    Thank you,
    Michael

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Advice (WIN 2000 Acc 97)

    If Group# and GroupNumber contain the same values, you should not put GroupName into both tables as well. The whole point of a relational database is that the data resides in one table and is used in other locations by relating the two tables on a shared index (i.e., Group#/GroupNumber). If you want to build an update query, I'd recommend you use the query grid rather than trying to write the SQL. It may use a different syntax, but it will save you a lot of headaches.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    Thanks Charlotte.

    MyTable is a "Full File" sent by a company. For whatever reason, they only include a field called Group #.
    The same company also sends us a "Group" File which I put into a table. It is a listing of Groups by Group Number AND the Group Name.

    Again, for whatever reason, I need to incorporate the Group Name into the Full File table. (MyTable).

    I actually did use the query grid, and the sql is what it generated. I believe it's easier to post sql than the grid itself, that's why I usually post the sql.

    Is there a way to update the MyTable to include the Group Name?

    We have asked this company to include Group Name in the Full File, but so far, they won't.
    So I have to do it.

    Thanks for any suggestions!

    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Advice (WIN 2000 Acc 97)

    Did you add a GroupName field to the design of MyTable? Otherwise, you won't be able to use update to populate it. You probably also need to use the DISTINCTROW keyword to make the query updateable.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    <hr>Did you add a GroupName field to the design of MyTable?<hr>
    Yes.
    I will try this again in the morning at work.
    I tried it just now at home, and it worked. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thanks from Sacramento!
    Michael

Posting Permissions

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