Crosstab query question

[CR]

I have a question about using crosstab query of course maybe there is another
way and any help would be great appreciated. The problem is that the
crosstab query is that it uses some type of function(sum, first, min) etc. I
need it to just show all the data. An example would be:

Instrument Date Result
Machine1 12/01/2009 20.1
Machine1 12/01/2009 22.5
Machine1 12/02/2009 23
Machine2 12/01/2009 25
Machine2 12/02/2009 24

I would like the query to show the following:

Machine1 Machine2
20.1 25
22.5 24
23

The fact that I have multiple results on the same date is causing me
problems.

Any suggestions?

Thanks

 

[KARL DEWEY]

Use a Ranking in a Group query to Temp table and then the crosstab query.

SELECT Q.Instrument, Q.Result, Q.YourDate, (SELECT COUNT(*) FROM [YourTable]
Q1
WHERE Q1.[Instrument] = Q.[Instrument]
AND Q1.[YourDate] < Q.[YourDate])+1 AS Rank INTO tblTemp
FROM YourTable AS Q
ORDER BY Q.Instrument, Q.YourDate;

TRANSFORM First(tblTemp.Result) AS FirstOfResult
SELECT tblTemp.Rank AS Entry
FROM tblTemp
GROUP BY tblTemp.Rank
PIVOT tblTemp.Instrument;

 

[Duane Hookom]

You need to have some value in your initial data that identifies why 20.1
comes before 22.5.

Do you have some type of primary key or other value that can be used? If
not, your problem is quite difficult to resolve without code.