Matching records in two tables.

[blackbox]

Hi All,

I have two tables consisting of:

Table: LONHA_Hist
Fields: Room, SeqID0101, SeqID0102, SeqID0103, SeqID0104, SeqID0105

In the LONHA_Hist table the fields hold:

Row1: 100, 1,1,1,1,0
Row2: 101, 1,0,1,1,0
Row3: 102, 0,1,1,0,1
Row4: 103, 1,1,0,1,0

Table: SequenceNo
Fields: SeqID, SeqHeader, SeqText

In table "SequenceNo" the fields hold:

Row1: SeqID0101, header text1, sequence text1
Row2: SeqID0102, header text2, sequence text2
Row3: SeqID0103, header text3, sequence text3
Row4: SeqID0104, header text4, sequence text4

and so on.

Is there a way I can produce a query that looks up all the fileds in in "LONHA_Hist table and matches the reords in
the "SequenceNO" table where, IF LONHA_Hist.SeqID0101 = 1, look up the records in the "SequenceNo" table for records
that match where "SequenceNO.SeqID" contains "SeqID0101" and so on.

Simply put, If SequenceNo.SeqiD content equals the name of the column in the LONHA_Hist table

With the results looking like this:

Row1: 100, 1,1,1,1,0,header text1, sequence text1
Row1: 102, 1,1,1,1,0,header text2, sequence text2
Row1: 103, 1,1,1,1,0,header text3, sequence text3
Row1: 104, 1,1,1,1,0,header text4, sequence text4

I hope I have written this clearly but I do understand its a little odd.

Thanks in advance,

Blackbox

[NogDog]

I suppose you could do it with some combination of character/text functions in the query (e.g. SUBSTRING()), but probably will not be able to use any indexes, so will be inefficient as the database size increases. Is there some reason you have to have different field formats for what you want to join on? It would be simpler and more efficient if both tables used, say, an integer field for that sequence ID, in which case a simple JOIN would work. Your application code could format the "SeqID0104" IDs with sprintf() or printf().