In this post, you’ll learn to solve a specific query raised by one of our reader. The query is as follows:
Lets see how to solve this..
As per the query raised, let’s try to create the two tables – Tbl_Client and Tbl_Activity. The dummy tables are as follows:
As you can see, the first table ‘TblClient’ contains the following fields:
ID (Primary Key – Uniquely Identifies each customer)
Full Name – This holds the full name of the customer.
Whereas the second table ‘TBLActivity’ contains the following fields:
Activity_ID (Primary Key – Uniquely identifies each transaction with each customer in the table)
ID – (Foreign key – This field in the table ‘TBLActivity’ uniquely identifies the row of another table ‘TblClient’. It establishes an one-to-many link between the two tables)
ActivityDate – This field list the date on which a particular activity was done with the client.
ActivityType – This fields describes the type of activity done with the client.
Now, you want to create a query such that the query should return the last activity done with the clients. Likewise, this needs to be done for all the clients.
This is very similar to the example discussed in the previous post : Select Top ‘n’ Records across each Group
The query for this example can be constructed as follows:
In the Outer query, you first select the field [FullName] from table TblClient, then select the fields [ActivityDate] and [ActivityType] from the other table TblActivity.
Since you want to find the final activity done with the client, you would be interested in knowing the final date on which you last dealt with that client and made an entry in the system. You would then find the activity corresponding to that final date. To do so, you should sort the field [ActivityDate] in Descending Order.
In the criteria section of the field [ActivityDate], specify another query – a Sub-query. This query would select the top 1 date for each of the customer. Since we are interested in final/ latest date, you would sort this query in DESCENDING order. You then enclose this query within the In() operator.
The final query is as follows:
On running this query, you get the desired output as follows:
Thus, in this way, you can create a query to extract the last activity done with the client.
Having done this, please share with everyone if you have any alternate way of achieving the same result.