[AccessD] Trying to avoid coding

[Rocky Smolin]

Definitely will work.  I find it easier to break those things up into two or
more queries.  Easier for me to maintain (old guy, you know).

r

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

Why joined?
I got this to work without a Join:

SELECT Table2.Field2, Table2.Field3, (Select Min(Field3) From (Select
Distinct Top 2 Field3 From Table2  Order By Field3 Desc) ) AS
2ndHighestDate, (Select Max(Field3) From (Select Distinct Top 2 Field3 From
Table2 Order By Field3 Desc) ) AS HighestDate FROM Table2;


On Jun 1, 2015 6:09 PM, "Rocky Smolin" <rockysmolin at bchacc.com> wrote:

> 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
>
> --
> 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