Appending records to new table using field names as values

[Dave]

I have one temporary table with field names that go like this: ABC, DEF, ABC2, DEF2,...ABC12, DEF12

In one record, each of these fields may or may not have a value. I would like to append to another table both the field (eg ABC) and the value in thatfield. Getting the value to another table is easy but I'm having real trouble changing a field name to a value, although I'm sure there's a way.

Any help would be much appreciated.

Thanks,

Dave

 

[Douglas J Steele]

Try creating a Union query along the lines of:

SELECT "ABC" AS FieldName, ABC As FieldValue
FROM MyTable
WHERE ABC IS NOT NULL
UNION
SELECT "DEF", DEF
FROM MyTable
WHERE DEF IS NOT NULL
UNION
SELECT "ABC2", ABC2
FROM MyTable
WHERE ABC2 IS NOT NULL
UNION
SELECT "DEF2", DEF2
FROM MyTable
WHERE DEF2 IS NOT NULL

You'll like have to create two queries like this, since I believe you can
only have 16 subqueries in a given Union query.
"Dave" wrote in message

I have one temporary table with field names that go like this: ABC, DEF,
ABC2, DEF2,...ABC12, DEF12

In one record, each of these fields may or may not have a value. I would
like to append to another table both the field (eg ABC) and the value in
that field. Getting the value to another table is easy but I'm having real
trouble changing a field name to a value, although I'm sure there's a way.

Any help would be much appreciated.

Thanks,

Dave

 

[Dave]

I have one temporary table with field names that go like this: ABC, DEF, ABC2, DEF2,...ABC12, DEF12 In one record, each of these fields may or may not have a value. I would like to append to another table both the field (egABC) and the value in that field. Getting the value to another table is easy but I'm having real trouble changing a field name to a value, although I'm sure there's a way. Any help would be much appreciated. Thanks, Dave

Douglas,

That Union as a subquery to an append query did the trick!
thank you so much. I had never done a union query before. I didn't really know what it was. Now I have another weapon in my arsenal.

Thanks again

Dave