[AccessD] Trying to avoid coding

[Rocky Smolin]

Yeah the record will need a column of last date and second to last date.  I
think the query that pulls the last two dates will work, joined to the
original query and then use Max and Min to get the last and second to last
dates.

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Steve Schapel
Sent: Monday, June 01, 2015 3:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Trying to avoid coding

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