Trying to reorganize data from 2 tables into 1

[technesurge]

Hello folks, new to access...

My datalogger is writing to Access through 2 tables: FloatTable and StringTable.

The column format for FloatTable is:


Date, Tagname, Value (a number)

The column format for StringTable is:
Date, TagName, Value (a string)

I want to combine the two tables by making a column for each TagName.


For example, FloatTable looks like this:
5/31 5pm, data1, 123
5/31 5pm, data2, 456
5/31 5pm, data3, 789

and StringTable looks like this:
5/31 5pm, string1, "Thanks"
5/31 5pm, string2, "For Your"
5/31 5pm, string3, "Help"


I want to reorganize the data with appropriate field names so that it looks like this:

date | string1 | string2 | string3 | data1 | data2 | data3
-------------------------------------------------------------------------------------
5/31 5pm "Thanks" "For Your" "Help" 123 456 789

[June7]

What are the field names of each table? I will use:
MyDate MyTime MyData MyValue
and
MyDate MyTime MyString MyValue

Try two crosstab queries then join the crosstabs on the MyDate and MyTime fields:
TRANSFORM First([MyValue]) AS FirstOfMyValue
SELECT [MyDate], [MyTime]
FROM Table1
GROUP BY [MyDate], [MyTime]
PIVOT [MyData];

TRANSFORM First([MyValue]) AS FirstOfMyValue
SELECT [MyDate], [MyTime]
FROM Table2
GROUP BY [MyDate], [MyTime]
PIVOT [MyString];

Other methods to denormalize data and arrange left-to-right (horizontal):

Review:
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html

Another method (seems I have dealt with this before in another thread) might be a query for each string and data then join them all on the date and time values.
SELECT MyDate, MyTime, MyValue As Data1 WHERE MyData = data1
SELECT MyDate, MyTime, MyValue As Data2 WHERE MyData = data2
SELECT MyDate, MyTime, MyValue As Data3 WHERE MyData = data3
SELECT MyDate, MyTime, MyValue As String1 WHERE MyData = string1
SELECT MyDate, MyTime, MyValue As String2 WHERE MyData = string2
SELECT MyDate, MyTime, MyValue As String3 WHERE MyData = string3

[ssanfu]

Be aware that "Date" and "Value" are reserved words in Access shouldn't be used for object names.


Also, in the second crosstab query, I think "Table1" should be "Table2":

TRANSFORM First([MyValue]) AS FirstOfMyValue
SELECT [MyDate], [MyTime]
FROM Table2
GROUP BY [MyDate], [MyTime]
PIVOT [MyString];

[June7]

Yes, ssanfu, copy/paste strikes again! I have corrected my post, thanks.

[technesurge]

Outstanding, thank you both very much!