That is going to be very difficult (?impossible?) using a crosstab
query. Crosstab's only return a set of columns for one field and you
seem to want three fields.

TheQuery refers to the query you are using to get the desired records -
mine or yours.

You might try a query that looks like the following

SELECT A.[Store #]
, A.[KO#]
, A.[Start]
, A.[End]
, A.Threshhold
, B.[Start]
, B.[End]
, B.Threshhold
FROM TheQuery as A INNER JOIN TheQuery as B
ON A.[Store #] = B.[Store #]
AND A.[KO#] B.[KO#]

Although my query did not include threshhold, I'm sure you can figure
out how to add it in. I did assume that KO# are increasing over time.
You could use END instead - AND A.End B.End - to get the results.


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


ragtopcaddy via AccessMonster.com wrote:
Thanks John.

I have solved returning the "raw" records that I need to crosstab. What I'm
having difficulty with is designing a crosstab query that will return the
records under headings "KO1" and KO2" regardless of whether they are
identified as KO# 2 and 3 in the table. So regardless of the KO# in the
record, the 'youngest' record will be under the heading "KO1", and the older
record under "KO2". If KO# 3 also falls within the date criteria, it should
be ignored. The spreadsheet only has room for KO1&2.

John Spencer wrote:
One method to get the TOP 2 is as follows.

SELECT tblKOs.[Store #]
, [KO#]
, tblKOs.Start
, tblKOs.End
FROM tblKOs
WHERE [KO#] in
(SELECT TOP 2 [KO#]
FROM tblKOs as A
WHERE A.[Store #] = tblKOs.[Store #]
AND A.End Between #6/1/2009# and #6/30/2011#
ORDER BY A.END DESC)

Use that query as the basis for returning the data you want.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===

Thanks, Karl

[quoted text clipped - 41 lines]
KO#s 2 and 3, such that it will correspond to my columns in the spreadsheet
template?