ACCESS FEED: Re: [MS_AccessPros] Query help determining last date across four fields

Minggu, 21 Juni 2015

Re: [MS_AccessPros] Query help determining last date across four fields

Rod-

I would "unpivot" the table with a UNION query and then use a Totals query on that:

qryUnpivot:

SELECT TaxpayerID, DateLtrSent As LastDate

FROM MyTable

UNION ALL

SELECT TaxpayerID, DateLtrDue As LastDate

FROM MyTable

UNION ALL

SELECT TaxpayerID, DateResponded As LastDate

FROM MyTable;

SELECT TaxpayerID, Max(LastDate) As HighDate

FROM qryUnpivot

GROUP BY TaxpayerID;

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jun 20, 2015, at 11:48 PM, desertscroller@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am attempting to develop a query for a report using four fields as the drive for the query. The fields are
        taxpayerID        integer
        DateLtrSent            date
        DateLtrDue             date
        DateResponded     date
The issue is I need to evaluate the three dates for each record and output the following
       taxpayerID
       latest of the three dates
I am trying to establish a stored query for use within vba. Not use of the best approach so I am trying some different methods. Any suggestions would be helpful.
Using windows 8.1 and 7 using Access 2010.
Thanks
Rod

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Visit Your Group