Solved Queries in Excel for MAX Date?

November 27, 2017 at 09:43:48
Specs: Windows 7
I have an excel workbook with several sheets. One sheet (ClientTbl) is set up as the parent table and all other sheets (each on separate tab) are set up as the child tables, i.e AddressTbl, EmployTbl, etc... The reason for this is that due to the nature of our contact with clients, it is likely that their address will change from one contact to the next or they will change employer from one contact to the next. Within the child tables, the date and time the information is entered is automatically record. I need to create a query to create one CURRENT record for each client and would like to take advantage of the pre-record date/time filed to ONLY report the record from each table that is the most recent. How would I go about doing this? I have put sample tables below

ClientTbl

ID	LastName	FirstName
1	Doe	John

AddressTbl

ID	Address	Date
1	15 N. Somewhere Ave	11/1/2017
1	756 Noplace Ave	11/27/2017

EmployTbl

ID	Employer	Date
1	Brown Company	10/20/2017
1	Orange Group	11/21/2017

Desired Query Results:

ID	LastName	FirstName	Address	Date	Employer	Date
1	Doe	John	756 Noplace Ave	11/27/2017	Orange Group	11/21/2017

Any help you can provide would be appreciated! Thank you!!!


See More: Queries in Excel for MAX Date?

Reply ↓  Report •

#1
December 1, 2017 at 07:56:20
✔ Best Answer
Could you please edit your data so that the columns line up correctly and include Column letters and Row numbers? That would make it easier for us to understand your layout (even if it's just an example). We may then be able to craft a solution that matches your example data. That helps to eliminate confusion, since we are all talking about the same data.

In the meantime, would something like this work for you? (I'm making assumptions as to your data layout and I probably shouldn't.)

Let's say you started with this data:

EmployTbl

         A              B                    C
1       ID	     Employer	            Date
2       1	   Brown Company	 10/20/2017
3       1	   Orange Group	         11/21/2017

This formula will return Orange Company:

=INDEX(B2:C3,MATCH(MAX(C2:C3),C2:C3,0),1)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question