[AccessD] Trying to avoid coding

[Rocky Smolin]

Steve:

OK - ran into a little problem.  To be more specific, the table has four
fields -  

1) fldBNYMeetingIDAutonumber ID (can be ignored), 
2) fldBNYID (FK to the tblBNY where BNY is a table of clients), 
3) fldBNYMeetingDate
4) fldBNYMeetingTypeID (FK to a table of meeting types.

So what I need is the last two meeting records for each fldBNYID. The query
needs all three fields 2, 3, and 4, in order to get the two dates needed by
Client to push out to the spreadsheet.  



Any ideas?

TIA

Rocky





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

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