ghnogueira
Registered User.
- Local time
- Today, 13:23
- Joined
- May 30, 2014
- Messages
- 10
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:
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!!!
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:
Code:
Process Contact date Salesman Client Client
Key Key
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!!!