How do I make a field represent other field names?

[Alpana]

I was designing a certain database, where I need to make a field (named 'Field') of a particular table represent and link the fields in other tables. For an example if one of the records contains an entry 'Name' in the 'Field' column, then that should be mapped to the value of 'Name' in another table... Can I do that using access?

[June7]

What do you mean by 'mapped'? You can use the value of the Field field to construct a name.

Field is probably a reserved word and should choose a different name for the field, such as TableField. I believe Name is also a reserved word, as is Date.

An example in VBA code:
x = DSum("[" & Me!TableField & "]", "tablename")

[Alpana]

Well, I basically want to create a field of fields. Your suggestion about the name of the field is helpful, but that does not solve my problem completely.

[June7]

Perhaps you should explain what you really need to accomplish and we can advise how. I already showed you how to use the TableField in an expression that will take its content as a field name for another table. How you get that field into the code is another matter. Is TableField bound to a control on a form, is it in a VBA coded recordset?

[Alpana]

Well, let me give you an example. Suppose there are two tables named Table1 & Table2. Table1 has the following fields & records:

MyName | MyDescription | Age
Mickey | Mouse | 75
Donald | Duck | 60

Now, table2 is defined this way:

Code | Prefix | FieldName | Suffix
1 | My name is | MyName | .
1 | I am | Age | years old.
1 | I am a | MyDescription | .

I want to concatenate all the records in table2 which have code 1, so as to finally get:
• My name is Mickey. I am 75 years old. I am a Mouse.
• My name is Donald. I am 60 years old. I am a Duck.
How can I do this?

[June7]

I was able to accomplish this with 5 queries with the sample data. If your data is more complex, will have to use elaborate VBA code involving recordsets and looping structures and a temp table. Something I am not going to tackle. You will have to learn programming or pay for a consultant or hope someone else wants the exercise. Here are some links to examples of similar situations:
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html

This requires a unique ID field in Table1. I used an autonumber field.

Query1
SELECT Table2.*, Table1.*
FROM Table1, Table2;

Sentence1
SELECT Query1.T1ID, Query1.Code, IIf([FieldName]="MyName",[Prefix],Null) & " " & IIf([FieldName]="MyName",[MyName],Null) & IIf([FieldName]="MyName",[Suffix],Null) AS Sentence1
FROM Query1
WHERE (((Query1.Code)=1) AND ((IIf([FieldName]="MyName",[Prefix],Null) & " " & IIf([FieldName]="MyName",[MyName],Null) & IIf([FieldName]="MyName",[Suffix],Null))<>"" And (IIf([FieldName]="MyName",[Prefix],Null) & " " & IIf([FieldName]="MyName",[MyName],Null) & IIf([FieldName]="MyName",[Suffix],Null))<>" " And (IIf([FieldName]="MyName",[Prefix],Null) & " " & IIf([FieldName]="MyName",[MyName],Null) & IIf([FieldName]="MyName",[Suffix],Null))<>" " And Not (IIf([FieldName]="MyName",[Prefix],Null) & " " & IIf([FieldName]="MyName",[MyName],Null) & IIf([FieldName]="MyName",[Suffix],Null)) Is Null));

Sentence2
SELECT Query1.T1ID, Query1.Code, IIf([FieldName]="Age",[Prefix],Null) & " " & IIf([FieldName]="Age",[Age],Null) & " " & IIf([FieldName]="Age",[Suffix],Null) AS Sentence2
FROM Query1
WHERE (((Query1.Code)=1) AND ((IIf([FieldName]="Age",[Prefix],Null) & " " & IIf([FieldName]="Age",[Age],Null) & " " & IIf([FieldName]="Age",[Suffix],Null))<>"" And (IIf([FieldName]="Age",[Prefix],Null) & " " & IIf([FieldName]="Age",[Age],Null) & " " & IIf([FieldName]="Age",[Suffix],Null))<>" " And (IIf([FieldName]="Age",[Prefix],Null) & " " & IIf([FieldName]="Age",[Age],Null) & " " & IIf([FieldName]="Age",[Suffix],Null))<>" " And Not (IIf([FieldName]="Age",[Prefix],Null) & " " & IIf([FieldName]="Age",[Age],Null) & " " & IIf([FieldName]="Age",[Suffix],Null)) Is Null));

Sentence3
SELECT Query1.T1ID, Query1.Code, IIf([FieldName]="MyDescription",[Prefix],Null) & " " & IIf([FieldName]="MyDescription",[MyDescription],Null) & IIf([FieldName]="MyDescription",[Suffix],Null) AS Sentence3
FROM Query1
WHERE (((Query1.Code)=1) AND ((IIf([FieldName]="MyDescription",[Prefix],Null) & " " & IIf([FieldName]="MyDescription",[MyDescription],Null) & IIf([FieldName]="MyDescription",[Suffix],Null))<>"" And (IIf([FieldName]="MyDescription",[Prefix],Null) & " " & IIf([FieldName]="MyDescription",[MyDescription],Null) & IIf([FieldName]="MyDescription",[Suffix],Null))<>" " And (IIf([FieldName]="MyDescription",[Prefix],Null) & " " & IIf([FieldName]="MyDescription",[MyDescription],Null) & IIf([FieldName]="MyDescription",[Suffix],Null))<>" " And Not (IIf([FieldName]="MyDescription",[Prefix],Null) & " " & IIf([FieldName]="MyDescription",[MyDescription],Null) & IIf([FieldName]="MyDescription",[Suffix],Null)) Is Null));

SentenceAll
SELECT [Sentence1] & " " & [Sentence2] & " " & [Sentence3] AS Expr1
FROM Sentence3 INNER JOIN (Sentence2 INNER JOIN Sentence1 ON Sentence2.T1ID = Sentence1.T1ID) ON Sentence3.T1ID = Sentence1.T1ID;

[Alpana]

Well, the solution worked after some slight modifications. Though this hard-codes the queries, but nonetheless, its useful. Thanks a lot.