Getting multiple rows into one row in select

[rbevers]

Given the following table and data:

TimeStamp HistoryPointID Value
8/1/2013 1:00:00 PM 1 19.15
8/1/2013 1:00:00 PM 2 117.1104
8/1/2013 1:00:00 PM 3 207.111
8/1/2013 1:00:00 PM 4 85.3264
8/1/2013 12:00:00 PM 1 20.1667
8/1/2013 12:00:00 PM 2 140.071
8/1/2013 12:00:00 PM 3 205.1211
8/1/2013 12:00:00 PM 4 85.3285



I would like to do a select where I would get;

TimeStamp, Value 1, Value 2, Value 3, Value 4
TimeStamp, Value 1, Value 2, Value 3, Value 4


or in other words using the actual data:
TimeStamp Value1 Value2 value3 value4
8/1/2013 1:00:00 PM 19.15 117.1104 207.111 85.3264
8/1/2013 12:00:00 PM 20.1667 140.071 205.1211 85.3285

I have tried group by TimeStamp and I just cant get it. Is there a way in Access to do this? In Microsoft SQL I could do it in the joins, but Access seems to have kittens if I try that.

Thanks in advance,
Bob

[June7]

Did you try a CROSSTAB query that groups on the TimeStamp field and pivots on the HistoryPointID and uses the First aggregate function on the Value?

[rbevers]

No I didn't because I have to do it in code and I don't know the SQL for that.

[June7]

Use Access query designer to construct the SQL statement. Switch to SQL View to see the statement. Copy/paste it into VBA and make necessary adjustments.

Alternatives:

1. emulate a crosstab http://www.datapigtechnologies.com/f.../crosstab.html:
SELECT TimeStamp, Last(IIf([HistoryPointID]=1,[Value],Null)) As Value1, Last(IIf([HistoryPointID]=2,[Value],Null)) As Value2, Last(IIf([HistoryPointID]=3,[Value],Null)) As Value3, Last(IIf([HistoryPointID]=4,[Value],Null)) As Value4 GROUP BY TimeStamp;

2. 4 queries, each with filter for a single HistoryPointID value (assuming there are only the 4 shown in example data), and a 5th query to create a dataset of unique TimeStamp values. Then a 6th query to join the 4 HistoryPointID queries to the distinct TimeStamp query.

3. write records to a temp table