crosstab query question

[ragtopcaddy via AccessMonster.com]

Store # KO# Threshold Start End
1019 1 $2,025,000.00 5/1/2007 4/30/2008
1019 2 $2,025,000.00 5/1/2008 4/30/2009
1019 3 $2,025,000.00 5/1/2010 4/30/2011

This is an example of some data in an Access table that I have to report to a
spreadsheet.

The spreadsheet has columns:

1st KO Threshold 1st KO Start 1st KO End 2nd KO Threshold 2nd KO
Start 2nd KO End

for each store reported to the spreadsheet.

As you can see, only 2 of the store records may be reported, and we would
like to take the 1st 2 records that fall between a set of dates. In this
instance KO#'s 2 and 3 satisfy the criteria.

How can I pivot this data in a crosstab query based on a query that returns
KO#s 2 and 3, such that it will correspond to my columns in the spreadsheet
template?

 

[ragtopcaddy via AccessMonster.com]

I think that what I need is a "Top 2" ascending for each store in the query.
Any idea how to do that?

 

[KARL DEWEY]

Use this query, changing the table name and adding your criteria for the set
of dates --
SELECT ragtopcaddy.Threshold AS [1st KO Threshold], ragtopcaddy.Start AS
[1st KO Start], ragtopcaddy.End AS [1st KO End], ragtopcaddy_1.Threshold AS
[2nd KO Threshold], ragtopcaddy_1.Start AS [2nd KO Start], ragtopcaddy_1.End
AS [2nd KO End]
FROM ragtopcaddy INNER JOIN ragtopcaddy AS ragtopcaddy_1 ON
ragtopcaddy.[Store #] = ragtopcaddy_1.[Store #]
WHERE (((ragtopcaddy_1.[KO#])=[ragtopcaddy].[KO#]+1));

 

[ragtopcaddy via AccessMonster.com]

Thanks, Karl

Only in this instance, for this particular store and a couple of others, do I
have to return KO2 and KO3. The rest of them I need to return KO1 and KO2.
IOW, I need to return 2 KO sets for records that have 3 KO sets. So I need to
return the 2 newest dates regardless of whether that means KO1 and KO2, or
KO2 and KO3. Does this query you designed do that? Here's the SQL modified
for the proper table name:

SELECT tblKOs.[Store #], tblKOs.Start AS [1st KO Start], tblKOs.End AS [1st
KO End], tblKOs_1.Start AS [2nd KO Start], tblKOs_1.End AS [2nd KO End]
FROM tblKOs INNER JOIN tblKOs AS tblKOs_1 ON tblKOs.[Store #] = tblKOs_1.
[Store #]
WHERE tblKOs_1.[KO#]=[tblKOs].[KO#]+1

Here's some sample data:
Store # KO# Start End
348 1 10/1/2009 9/30/2010
348 2 10/1/2010 11/29/2010
1200 2 8/1/2007 7/31/2009
1200 3 8/1/2008 7/31/2010

For this sample, the query should return:

Store # KO1 Start KO1 End KO2 Start KO2 End
348 10/1/2009 9/30/2010 10/1/2010 11/29/2010
1200 8/1/2007 7/31/2009 8/1/2008 7/31/2010

WHERE End>=#6/1/2009# And End<=#6/30/2011#

Use this query, changing the table name and adding your criteria for the set
of dates --
SELECT ragtopcaddy.Threshold AS [1st KO Threshold], ragtopcaddy.Start AS
[1st KO Start], ragtopcaddy.End AS [1st KO End], ragtopcaddy_1.Threshold AS
[2nd KO Threshold], ragtopcaddy_1.Start AS [2nd KO Start], ragtopcaddy_1.End
AS [2nd KO End]
FROM ragtopcaddy INNER JOIN ragtopcaddy AS ragtopcaddy_1 ON
ragtopcaddy.[Store #] = ragtopcaddy_1.[Store #]
WHERE (((ragtopcaddy_1.[KO#])=[ragtopcaddy].[KO#]+1));

Store # KO# Threshold Start End
1019 1 $2,025,000.00 5/1/2007 4/30/2008

[quoted text clipped - 18 lines]

KO#s 2 and 3, such that it will correspond to my columns in the spreadsheet
template?

 

[John Spencer]

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

Only in this instance, for this particular store and a couple of others, do I
have to return KO2 and KO3. The rest of them I need to return KO1 and KO2.
IOW, I need to return 2 KO sets for records that have 3 KO sets. So I need to
return the 2 newest dates regardless of whether that means KO1 and KO2, or
KO2 and KO3. Does this query you designed do that? Here's the SQL modified
for the proper table name:

SELECT tblKOs.[Store #], tblKOs.Start AS [1st KO Start], tblKOs.End AS [1st
KO End], tblKOs_1.Start AS [2nd KO Start], tblKOs_1.End AS [2nd KO End]
FROM tblKOs INNER JOIN tblKOs AS tblKOs_1 ON tblKOs.[Store #] = tblKOs_1.
[Store #]
WHERE tblKOs_1.[KO#]=[tblKOs].[KO#]+1

Here's some sample data:
Store # KO# Start End
348 1 10/1/2009 9/30/2010
348 2 10/1/2010 11/29/2010
1200 2 8/1/2007 7/31/2009
1200 3 8/1/2008 7/31/2010

For this sample, the query should return:

Store # KO1 Start KO1 End KO2 Start KO2 End
348 10/1/2009 9/30/2010 10/1/2010 11/29/2010
1200 8/1/2007 7/31/2009 8/1/2008 7/31/2010

WHERE End>=#6/1/2009# And End<=#6/30/2011#

Use this query, changing the table name and adding your criteria for the set
of dates --
SELECT ragtopcaddy.Threshold AS [1st KO Threshold], ragtopcaddy.Start AS
[1st KO Start], ragtopcaddy.End AS [1st KO End], ragtopcaddy_1.Threshold AS
[2nd KO Threshold], ragtopcaddy_1.Start AS [2nd KO Start], ragtopcaddy_1.End
AS [2nd KO End]
FROM ragtopcaddy INNER JOIN ragtopcaddy AS ragtopcaddy_1 ON
ragtopcaddy.[Store #] = ragtopcaddy_1.[Store #]
WHERE (((ragtopcaddy_1.[KO#])=[ragtopcaddy].[KO#]+1));

Store # KO# Threshold Start End
1019 1 $2,025,000.00 5/1/2007 4/30/2008

[quoted text clipped - 18 lines]

KO#s 2 and 3, such that it will correspond to my columns in the spreadsheet
template?

 

[ragtopcaddy via AccessMonster.com]

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.

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]

 

[John Spencer]

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.

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.

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?

 

[ragtopcaddy via AccessMonster.com]

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.

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]