[AccessD] Trying to avoid coding

[Stuart McLachlan]

I second the TOP TWO subquery appraoch.



On 2 Jun 2015 at 9:05, Steve Schapel wrote:

> 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 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>