Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    19

    Unanswered: query problem matching

    is it possible to get the rate from a column by matching data in column a to the actual name of another column?

    For instance, I have 4 columns named b, c, d and e. in column a i have a value that can be b, c, d and e. I want to match the data in column a with the appropriate column name and get the data in the matching column.

    Thanks.

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    This could do it. It will give an empty string if column a is not b,c,d or e
    Code:
    SELECT a, IIf(a='b',b,IIf(a='c',c,IIf(a='d',d,IIf(a='e',e,''))))
    FROM myTable
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Feb 2006
    Posts
    19
    thanks, I'm not sure that will help, I didn't make myself clear. I'm querying off of 2 tables. Column a has a text field. In a new column I want to get a rate from one of many columns when the data in column a matches the column name of one of the columns from another table....

    Thanks.

  4. #4
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    It should still be very similar. Just add in the join and change the field names. You can change the empty string at the end to return what ever the "default" column would be if you'd like to.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  5. #5
    Join Date
    Feb 2006
    Posts
    19
    Can this be done in access without using vb? if not I may be back

  6. #6
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    This is all in Access. No VB needed.
    Code:
     P.ProductName, IIf(RT.RateType='A', RT.RateType.A, IIf(RT.RateType='B', RT.RateType.B, ...)
    FROM Products P
      INNER JOIN RateTypes RT On P.RateType = RT.RateType
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  7. #7
    Join Date
    Feb 2006
    Posts
    19
    I may not understand the responses... I'm having rates entered into a form (table). Each entry has it's own column in the table. I'm trying to run a query that takes the rate entered in the from when the column name is = to the same name in the date (not the column name) of another table, so there's nothing similar to link on. Is there an easier way to set up the table that the form uses to query on after?

    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
  •