If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
crosstab query question
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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#2
|
|||
|
|||
crosstab query question
I think that what I need is a "Top 2" ascending for each store in the query.
Any idea how to do that? ragtopcaddy wrote: 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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
crosstab query question
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)); -- KARL DEWEY Build a little - Test a little "ragtopcaddy via AccessMonster.com" wrote: 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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#4
|
|||
|
|||
crosstab query question
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# KARL DEWEY wrote: 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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
crosstab query question
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 '================================================= === ragtopcaddy via AccessMonster.com wrote: 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# KARL DEWEY wrote: 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? |
#6
|
|||
|
|||
crosstab query question
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? -- Bill Reed "If you can't laugh at yourself, laugh at somebody else" Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#7
|
|||
|
|||
crosstab query question
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? |
Thread Tools | |
Display Modes | |
|
|