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