Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41

    Question Unanswered: How to select a field in a query based on another fields value

    Ok, Ive done this before, but im drawing a blank on how to do it. I have a field called NumberOfLevels and it is an integer between 1 and 20. I then have another table with Fields called Level1 all the way to Level20. I am trying to create a query that pulls Levelx field where x = NumberOfLevels.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Build your SQL string in VBA, bit by bit. Assign the string to SQL
    Code:
    DoCmd.RunSQL MySQLString...
    or
    Code:
    CurrentDb.OpenRecordset(MySQLString, dbOpenDynaset)
    only after you're satisfied that it's complete.

    Sam

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note - if you were not violating first normal form you would not need to:
    http://www.tonymarston.net/php-mysql...se-design.html

    Another alternative, without VBA, is some thing like:
    Code:
    SELECT SWITCH(NoOfLevels = 1, [Level1], NoOfLevels = 2, [Level2], NoOfLevels = 3, [Level3], ....) AS level_thingy
    FROM myTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Quote Originally Posted by Sam Landy
    Build your SQL string in VBA
    This is what I figured I would have to do, I just cant remember how to create a field name from 2 strings ie ("Level" & NumberOfLevels)

  5. #5
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Quote Originally Posted by pootle flump
    Note - if you were not violating first normal form you would not need to:
    http://www.tonymarston.net/php-mysql...se-design.html
    I kinda know about database design, in this particular situation I couldnt figure out how to have it where one way round it is 20 numerically incrementing fields or the other way where it would be 4+ non numerical fields. I figured it would be easier this way. Im more than open to suggestions as to how to still abide by the first normal rule in this situation.

    PS. I really like that link you gave. It has a lot of good information in there that for the most part I knew, but have never seen it put into words like that

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blade1981m
    PS. I really like that link you gave. It has a lot of good information in there that for the most part I knew, but have never seen it put into words like that
    Yeah - one of the other regs sometimes links to it but I only got round to reading it recently. I quite fancy having a read of the rest of the site too now.

    OK - there isn't much detail to work from so to start off:
    Instead of your current Level1, level2, level3 table why can you not have a structure of (LevelNo, SomeAttribute). 20 rows. LevelNo values of 1 to 20. SomeAttribute is the corresponding value in the current Level1, Level2, Level3 etc columns. You can now have a foreign key relationship too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by blade1981m
    This is what I figured I would have to do, I just cant remember how to create a field name from 2 strings ie ("Level" & NumberOfLevels)
    Don't forget you're building a SQL statement as a string BEFORE you identify it as SQL material. Hence, "SELECT level" & Me.LevelNo & " FROM some more info here...", after being built, still before being assigned to SQL, becomes "SELECT level20 FROM some more info here..."

    Or whatever the level is.

    Sam

  8. #8
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Quote Originally Posted by pootle flump
    Instead of your current Level1, level2, level3 table why can you not have a structure of (LevelNo, SomeAttribute). 20 rows. LevelNo values of 1 to 20. SomeAttribute is the corresponding value in the current Level1, Level2, Level3 etc columns. You can now have a foreign key relationship too.
    Ok, I dont know why, but I cant figure out how to get this to work with the way my table is currently setup. I know this is probably something so amazingly simple that just isnt clicking with me so I have created a database consisting of the table in question along with an example of the table that links to it. What I am looking to do is have a query that has character name and attack bonus listed. So for the three characters I have, their respective attack bonuses should be 3, 5 and 4

    Quote Originally Posted by Sam Landy
    "SELECT level" & Me.LevelNo & " FROM some more info hereSam
    Thanks. Thats what I was looking for. For some reason I kept forgetting the part about having to build the sql statement before running it.
    Attached Files Attached Files

  9. #9
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    OK pootle, I think I got it figured out. I have no idea why I was finding it so difficult to visualize in my head, but when i started to lay it out on paper, it was very obvious to me how to split it down and still abide by the first normal rule. Thanks for pointing out the error in my design.

    And thanks again to Sam. I may never need to use that due to the first normal rule, but its good to know how to do it just in case.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blade1981m
    OK pootle, I think I got it figured out. I have no idea why I was finding it so difficult to visualize in my head, but when i started to lay it out on paper, it was very obvious to me how to split it down and still abide by the first normal rule. Thanks for pointing out the error in my design.
    Good stuff - glad you got it. I had redesigned your table and was about to populate it but you saved me the effort!

    You will find as design more that this sort of 1NF issue is dead easy to spot and you should be in a better position to sort it next time too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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