Reformatting data into logical records

[Bob Alston]

I have a local nonprofit that has a database with a rather unusual
record structure. The data about their customers is stored as one value
per record.

So the access table definition is:
Client#
DataValueType
DataValue

The data would look like this:
Client# DataValueType DataValue
12345 streetaddr 1001 s First St.
12345 City Tulsa
12345 State OK
12345 Zip 74101
12345 StartDate 1/1/2005
12345 TypeCust Retail
12345 Salesman Bill

and so on for each customer.
the net result is a very large file. And almost impossible to identify
missing data.

I would like to convert it into a normal record, something like this:

client#
Streetaddr
City
Zip
State
Startdate
TypeCust
Salesman

And a view of a record would look like the following if the table
contents were viewed:

Client Streetaddr City State Zip startdate typecust...
12345 1001 s First St Tulsa OK 74101 1/2/2005 Retail....
Bill

Anyone know any EASY ways to reformat the records?

Seems to me I would need to use VBA code to read the records, construct
an array, then write out a new record whenever a new client data starts.

Suggestions

[Bob Alston]

Bob Alston wrote:

I have a local nonprofit that has a database with a rather unusual
record structure. The data about their customers is stored as one value
per record.

So the access table definition is:
Client#
DataValueType
DataValue

The data would look like this:
Client# DataValueType DataValue
12345 streetaddr 1001 s First St.
12345 City Tulsa
12345 State OK
12345 Zip 74101
12345 StartDate 1/1/2005
12345 TypeCust Retail
12345 Salesman Bill

and so on for each customer.
the net result is a very large file. And almost impossible to identify
missing data.

I would like to convert it into a normal record, something like this:

client#
Streetaddr
City
Zip
State
Startdate
TypeCust
Salesman

And a view of a record would look like the following if the table
contents were viewed:

Client Streetaddr City State Zip startdate typecust...
12345 1001 s First St Tulsa OK 74101 1/2/2005 Retail....
Bill

Anyone know any EASY ways to reformat the records?

Seems to me I would need to use VBA code to read the records, construct
an array, then write out a new record whenever a new client data starts.

Suggestions

Solved. Use a cross tab query. Then base a new query on the cross tab
query.

Bob