Query that returns the first record of each client depending on a filter

[ghnogueira]

Hello!



I have a database where I need to return only the first record of each key (named "process key") accordingly to a time frame I set.

Consider the table below:

Process Key	Contact date	Salesman	Client Key	Client
999	04/28/2014 11:39:00	SALESMAN 1	111	CLIENT 1
888	04/28/2014 10:13:00	SALESMAN 1	222	CLIENT 2
555	04/22/2014 14:29:00	SALESMAN 2	333	CLIENT 3
666	04/16/2014 12:14:00	SALESMAN 3	444	CLIENT 4
555	04/16/2014 11:47:00	SALESMAN 2	333	CLIENT 3

If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).

If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).

If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).

I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.

What should I do?

Thanks!!!

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

[lfpm062010]

Try this, let's call the table "CLIENTS". (Not tested)

SELECT CLIENTS.[Process Key], First(CLIENTS.[Contact date]) AS [FirstOfContact date], First(CLIENTS.Salesman) AS FirstOfSalesman, First(CLIENTS.[Client Key]) AS [FirstOfClient Key]
FROM CLIENTS
HERE CLIENTS.[Contact date] BETWEEN [DATE BEGIN] AND [DATE END]
GROUP BY CLIENTS.[Process Key]
ORDER BY CLIENTS.[Process Key], First(CLIENTS.[Contact date]) DESC;

[ghnogueira]

Try this, let's call the table "CLIENTS". (Not tested)

SELECT CLIENTS.[Process Key], First(CLIENTS.[Contact date]) AS [FirstOfContact date], First(CLIENTS.Salesman) AS FirstOfSalesman, First(CLIENTS.[Client Key]) AS [FirstOfClient Key]
FROM CLIENTS
HERE CLIENTS.[Contact date] BETWEEN [DATE BEGIN] AND [DATE END]
GROUP BY CLIENTS.[Process Key]
ORDER BY CLIENTS.[Process Key], First(CLIENTS.[Contact date]) DESC;

Perfect mate!!!

Thanks a lot!!!

[pbaldy]

I'd be careful with that:

First and Last Functions Return Unexpected Records

[ghnogueira]

I'd be careful with that:

First and Last Functions Return Unexpected Records

Hey mate! So far all my results have been correct with the First function there...