Need to find name of field

[TrollBro]

Hi, I hope someone can help me with this. I have a table with 26 fields in each record. Is there a way to find the field name in each record that has a specific value? for example, 26 fields named 'a', 'b', ... 'z'. each field has a different numeric value. I want the field name for each record that has a value of 25. The 1st record's field with 25 is field "K", so I want "K" as my result. The 2nd record's field with 25 is field "W", so I want "W" as my result for this record.

Is there a simple way to do this?

Thanks!

 

[Golom]

Not with just SQL. SQL returns field values ... not field names. You would need to use a recordset and some VB.

Seems to me though that you would just get a list of letters with no predictable order. Relational databases do not guarantee any particular ordering of records without an ORDER BY clause.

Anyway

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fd As DAO.Field
Dim Values() as String
Dim n As Integer

Set db = Currentdb()
Set rs = db.OpenRecordset("Select * From myTable")

Do Until rs.EOF
   For each fd in rs.Fields
      If fd.Value = 25 then
         Redim Preserve Values(n)
         Values(n) = fd.Name
         n = n + 1
         Exit For
      End If
   Next
   rs.MoveNext
Loop

Your field names are now in the Values array but all you really know from that is that, somewhere in the table there is a field named "K" (for example) that has the value 25.

 

[dhookom]

The simple solution would be to normalize your table structure so you don't have 26 similar fields to search.
Also, there is no such thing as "first" and "second" records. Records are like marbles in a bag with no order.

Can we assume you at least have a Primary Key field?

For you current issue, I would create a normalizing union query [quniA2Z]:

SELECT PKField, [A] as TheValue ,"A" as TheField
FROM tblSpreadsheet
UNION ALL
SELECT PKField, [B],"B"
FROM tblSpreadsheet
UNION ALL
SELECT PKField, [C],"C"
FROM tblSpreadsheet
UNION ALL
SELECT PKField, [D],"D"
FROM tblSpreadsheet
UNION ALL
'etc
SELECT PKField, [Z],"Z"
FROM tblSpreadsheet;

You can then query the union query like:

SELECT *
FROM quniA2Z
WHERE TheValue = 25

Duane
Hook'D on Access
MS Access MVP