Thanks again.
I think that what I really need is a substitute for the KO# in the crosstab
query below. What I'm doing is writing separate crosstab queries for each of
interest, and combining all of the crosstab queries in another query. This
one is for the date field "End":
TRANSFORM First(End) AS FirstOfEnd
SELECT [Store #]
FROM qryKOs
GROUP BY [Store #]
PIVOT "KO" & [KO#] & " End"
The problem with the query is that it returns "KO3 End" where the KO# in the
query is 3. Also, it will return 3 columns if KO#'s include 1, 2, and 3.
qryKOs is a query on the sample data I posted earlier. Ignore "Threshold". I
abbreviated the # of fields of interest for the sake of simplicity. I'm
trying to return at most 2 columns, labeled "KO1 End" and "KO2 End",
regardless of the KO# in the query. If only 1 date field falls within the
criteria, then it should return only "KO1 End", even if the KO# in the query
is 3.
John Spencer wrote:
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
'================================================ ====
Thanks John.
[quoted text clipped - 34 lines]
KO#s 2 and 3, such that it will correspond to my columns in the spreadsheet
template?
--
Bill Reed
"If you can't laugh at yourself, laugh at somebody else"
Message posted via
http://www.accessmonster.com