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

Minggu, 21 Juni 2015

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

Thanks John, that is something new for me. That technique looks very interesting. I will be trying it tomorrow.
Thanks again
Rod

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

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@... [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