Here is a very difficult question indeed. I have a form based on a query. I have 22 fields located in the main table, and a secondary table that contains the description of what the codes contain. There is a HUGE amount of duplication in table 1. Table 1 represents records of users, what database they use, their name, and 20 clusters (NAMED CLUSTER1, CLUSTER2, etc.) per name+database (this is a text file that I pulled off a mainframe, by the way). Each user can have up to 6 records per database. What I want to do is set up my Access form with controls (maybe text boxes, labels) that update automatically when I page thru the records from the records in the second table w/their respective descriptions.
EXAMPLE
On my form I have the following:
BASE RIM
NAME DOUG
CLUS1 CLDSR <NOTE: EACH CLUSTER IS A 5 LETTER CODE>
CLUS2 VCLSW
These are records from the 1st table.
In my second table, the value of CLDSR tells me that the value is RIM70 in the MENUNAME field. I would then need to place a control on the form that is updated according to each field's cluster code, and display the description. I need to be able to (Bearing in mind there are 5 fields [surnamed CODE1, CODE2, etc.], and 20 field clusters in the 1st table) walk the code thru the first field (CLUS1), compare it w/the value in the 1st field in table 2 (CODE1), and display the description if found, if not found, step over to table 2 field 2 (CODE2), and compare it again, and walk thru all 5 fields to compare that value. THEN I have to do the same w/the second field in Table 1 (CLUS2). Then I'd have to do this for all 20 fields. I'd build an action query, but as anyone can see, that would be a monumental and tedious task. Anyone have any ideas?
EXAMPLE
On my form I have the following:
BASE RIM
NAME DOUG
CLUS1 CLDSR <NOTE: EACH CLUSTER IS A 5 LETTER CODE>
CLUS2 VCLSW
These are records from the 1st table.
In my second table, the value of CLDSR tells me that the value is RIM70 in the MENUNAME field. I would then need to place a control on the form that is updated according to each field's cluster code, and display the description. I need to be able to (Bearing in mind there are 5 fields [surnamed CODE1, CODE2, etc.], and 20 field clusters in the 1st table) walk the code thru the first field (CLUS1), compare it w/the value in the 1st field in table 2 (CODE1), and display the description if found, if not found, step over to table 2 field 2 (CODE2), and compare it again, and walk thru all 5 fields to compare that value. THEN I have to do the same w/the second field in Table 1 (CLUS2). Then I'd have to do this for all 20 fields. I'd build an action query, but as anyone can see, that would be a monumental and tedious task. Anyone have any ideas?
