[AccessD] Trying to avoid coding

[Bill Benson]

Ah .... I get it now (I think) - so these are to be additional columns in
the Select clause?

My bad, thanks for setting me straight (with remarkable courtesy!)
On Jun 1, 2015 6:04 PM, "Steve Schapel" <steve at datamanagementsolutions.biz>
wrote:

> Hi Bill
>
> I had understood that Rocky still wanted the exported data to include
> records in addition to those with the most recent dates.
>
> Regards
> Steve
>
> -----Original Message----- From: Bill Benson
> Sent: Tuesday, June 2, 2015 9:38 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Trying to avoid coding
>
> Maybe a little more concise, if you are not averse to IN operator:
>
> do your normal query and add in the where clause
>
> Where DateField in (Select distinct TOP 2 DateField From YourTable order by
> YourTable.DateField)
> On Jun 1, 2015 5:07 PM, "Steve Schapel" <steve at datamanagementsolutions.biz
> >
> 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
>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>