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? |
#8
|
|||
|
|||
crosstab query question
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 |
Thread Tools | |
Display Modes | |
|
|