Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Question Unanswered: Match Field Name with Data in another field

    Okay, I hope this makes sense, I have a value table with 5 different fields, named A, B, C, D and F. Each has a value in them.

    I have another table with master detail transaction information: name, addr, etc and an incident field with either an A, B, C, D or F as the value.

    I am making a report and depending whether I have an A, B, C, D or F in the incident field for one of the master detail records, I want to match the field named either A, B, C, D or F and print out it's VALUE.

    So, if it is an A, find the A field name in the value table and print the value stored in the A field.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is it too late to change the design? The first table should have a record for each value, with two fields: the value "A" and its related value. Then it's a simple query join to get the related values.
    Paul

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    At this point I am stuck with this design, because each field A,B,C,D or E can hold a different number... they are refreshed by external data feeds.

    So, yes, still need help with this, thanks.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, what I meant was that table would contain 5 records with 2 fields each:

    A 123
    B 456
    C 789
    D 111
    F 777

    Then you have a simple join to get the data. If you're still stuck with it, my first option would be to create a UNION query that took the flat data and made it like the above, which still gives you a simple join. The only other thing that comes to mind is using the value from the incident field in the first argument of a DLookup, but I've never tried to do that.
    Paul

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You 'might' be able to do some coding in the OnFormat event of the report...

    if me!MyMasterFieldName = "A" then
    me!MyNumberField = dlookup("[A]","ABCDEFTableName")
    end if
    if me!MyMasterFieldName = "B" then
    me!MyNumberField = dlookup("[B]","ABCDEFTableName")
    end if

    or
    Select Case me!MyMasterFieldName
    Case "A"
    me!MyNumberField = dlookup("[A]","ABCDEFTableName")
    case "B"
    me!MyNumberField = dlookup("[B]","ABCDEFTableName")
    ....
    End Select

    It's too bad you can't change the design as Paul suggested. That would be ideal.
    Last edited by pkstormy; 04-13-10 at 18:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2003
    Posts
    300
    Oh, now I see what you are saying, yes, I will try it this way.. Of course post my results.

    Thanks very much for your help!

Posting Permissions

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