Rows to Columns (crosstab) with many rows

The third-party application I'm working with (this is NOT Access, but uses the MDAC library) has three Access tables structured like this:

TagTable

TagName TagIndex TagType  
peach   0        Float  
apple   1        Float  
grape   2        Float  
cherry  3        String

and so on for many more rows

FloatTable

DateAndTime         TagIndex Value
03/02/2012 14:03:03 0        123.456
03/02/2012 14:03:03 1        123.456
03/02/2012 14:03:03 2        123.456
03/02/2012 14:06:05 0        456.789
03/02/2012 14:06:05 1        456.789
03/02/2012 14:06:05 2        456.789

And so on for many more rows and times

StringTable

DateAndTime         TagIndex Value
03/02/2012 14:03:03 3        'Some string...'
03/02/2012 14:06:05 3        'Some other string'

And so on for many more rows and times

What I want to do is execute a query or run code (VBA) to get to a recordset like this:

DateAndTime         peach   apple   grape   cherry
03/02/2012 14:03:03 123.456 123.456 123.456 'Some string...'
03/02/2012 14:06:05 456.789 456.789 456.789 'Some other string'

And so on.

Normally, I would do this with "self join"s or unions no problem. The problem is that there will be at least 90+ rows in the TagTable so 90+ columns in the result. I can't see doing 90+ joins in a query, even if Access could do that. I CAN run some VBA code within the application, if needed. I looked into using use TRANSFORM...PIVOT, but there is no aggregation going on. I looked at a "crosstab" query also, but this will get complicated with 90+ rows/columns. I know I can do this with VBA, but I was hoping that there is some Jet SQL magic that I can use that will help.

Solution

It seems you wish to use a crosstab to denormalize data. If the intersection of peach and time is just one value, that is, there are not multiple records for peach with the same time, you can use First(Value), Min(Value) or Max(Value) to return the correct result. This is the aggregate function that MS Access is looking for.