[AccessD] Trying to avoid coding

[Steve Schapel]

Hi Rocky

Is this suitable?...

Make an interim query like this:

SELECT DISTINCT TOP 2 YourDateField FROM YourTable ORDER BY YourDateField 
DESC

Then add that query to your export query, and add these columns:

Max([YourInterimQuery].[YourDateField])
Min([YourInterimQuery].[YourDateField])

This should give you the last date and the second last date from the table.

Regards
Steve


-----Original Message----- 
From: Rocky Smolin
Sent: Tuesday, June 2, 2015 8:08 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Trying to avoid coding

Dear List:

I'm exporting stuff to a spreadsheet and using a query and that's working
just fine.  The user wanted the last date in one of the tables so a
summation query, grouping on all the fields except the date field and using
MAX on the field with the dates worked out nice.

Now he wants both the last date and the previous date (users (sigh) gotta
love 'em) - IOW the last two dates.  I can do this with code and push the
values into a spreadsheet through automation, but that TransferSpreadsheet
is just soooooo easy and I'm getting lazier in my old age.

So is there a way to pull the last two values from a field in a table when
you sort on that field with a query, getting the last value in one of the
columns and the second to last in another?

MTIA

Rocky Smolin
Beach Access Software
858-259-4334
www.bchacc.com <http://www.bchacc.com/>
www.e-z-mrp.com <http://www.e-z-mrp.com/>
Skype: rocky.smolin

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com