How do I put many simple records into columns???

[technesurge]

Hello all,

My datalogger takes a snapshot and it creates a table like this:

DataAndTime, DataName, DataValue
-----------------------------------------
6/19/2012 9:00:00AM, DataName1, X
6/19/2012 9:00:00AM, DataName2, Y


.
.
.
6/19/2012 9:00:00AM, DataName68, Z

Unfortunately I have no control over how the datalogger writes. I want to reorganize the data so that I can have each snapshot as 1 record:

DateAndTime, DataName1, DataName2...DataName68
-------------------------------------------------------------
6/19/2012 9:00:00AM, X, Y... Z

Initially I had success.
I used the following code to select each datapoint:

SELECT Table.DateAndTime, Table.DataValue AS DataName1
FROM Table
WHERE (((Table.DataName)="DataName1");

I created a query like the one above for each DataName. Then I made a "master" query by relating all the sub queries by DataAndTime, and stacked them together in one wide master query. Everything was fine except somewhere around 30 of these Access complains the query is too complex.

There must be another way to do this. Is there a way to run a VB script anytime a table changes?

[John_G]

Hi:

"I want to reorganize the data so that I can have each snapshot as 1 record:"

My suggestion - don't do it that way, it is very poor database design (unnormalized), which can lead to problems as you have seen.

Keep each record as it is, with the three fields. You can use a cross-tab query to display the data the way you need it.

John

[technesurge]

Thank you very much!