A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

crosstab query question



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2008, 06:52 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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  
Old July 8th, 2008, 07:01 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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  
Old July 8th, 2008, 08:26 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old July 8th, 2008, 09:38 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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  
Old July 8th, 2008, 11:22 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 2,364
Default 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  
Old July 9th, 2008, 02:57 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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  
Old July 9th, 2008, 03:25 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 2,364
Default 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  
Old July 9th, 2008, 07:37 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.