I am trying to find what is the best approach to handle the case I am working on. What I am trying to do is to display all of the data/fields but add the count of how many time a material being sold and returned from our shop.
Here is my select statement:
Select MaterialName, MaterialType, MaterialTypeID, BuyerID, Status, StatusDate, DepartmentID, WarehouseRepID
This is the data from our material table:
So what I am trying to do is to create a list that shows count on each material type id status like below. There is a new field called " Status Count". This field is to count total of each MaterialType ID. For example: Material ID: CB013. This material being sold for 3 times on 03/21/2015, 06/11/2015 and 08/02/2015 but also customer returned this item for 3 times on 04/15/2015, 06/20/2015 and 08/28/2015. As you can see, in the Status count field, this item count as 3 on status Sold and 3 on status Returned. This logic is apply to other equipment's as well. It counts the total of the status. So this is what I am trying to do. Anyone could help?