Counting of Rows on Same Values

Question

Hi,I have 2 tables Employee and Task. Each Employee has many Tasks and those Tasks are defined weekly.In the Task Table I am trying to capture the count of the tasks by Employee Name+Week Number+Colour. So if an Employee has multiple tasks which results in the example like below.Row 1 Gunsham Lakhiani-14-GreenRow 2 Gunsham Lakhiani-14-GreenThis should be counted as 2 which is what i am trying to achieve by adding a column of Total Count which results in to 2 in each row. I would like to have the count 2 only in one row and not in the second row after which I want to just dump Employee Name,Color,Week Number and Total Count into some other table where the Total Count is not blank for certain calculations.Pl check the excel attachment or the Image below for your reference with the QB Ouput and the Desired Output.RegardsGunsham9821258653------------------------------Gunsham Lakhiani------------------------------

markshnier__you · Answer

I suggest that you use a Summary report. If you cannot get enough "group bys" (I think that the limit is 6) for the columns that you need, you can combined fields in a formula.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

gunshamlakhiani · Answer

Hi Mark,My problem is not a Summary Report or enough Group Bys. What i want to do is I would like to have a Count only on the one row for which the combination is the same and then i would like to insert those rows in another table.For example if you have a look at the screen shot on the Top Grid QB Output you would see that Gunsham Lakhiani-14-Green shows Total Count 2 in both the rows but I want the Total Count 2 in any one row and then would like to add that in another table as belowName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Week&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Color&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CountGunsham Lakhiani&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Green&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2I hope i am able to explain what i am trying to achieve.Looking forward to hear from you.RegardsGunsham------------------------------Gunsham Lakhiani------------------------------

markshnier__you · Answer

Let us not give up on the summary report approach quite yet. My point of making a summary report is once you have that you can use the MORE button to copy these records to another table. Can you tell me if there is a reason that approach will not work for you?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

gunshamlakhiani · Answer

Hi Mark,I do not want any manual intervention but it should be automated thru pipelines or any other way.Thanx------------------------------Gunsham Lakhiani------------------------------

markshnier__you · Answer

It can be done them using Formula Queries.&nbsp; You would need one Formula Query to identify the Record with the Minimum Record ID# for the Unique combination.&nbsp; Then another Formula Query to get the sum of the qty for those in the same "unique combination" of records.&nbsp;It's beyond the scope of what I can do on this Forum to provide the code for the Formula Queries.If you search this forum for Formula Query unique you will probably find some posting on that aspect.&nbsp; If you decide to go that route I can assist if you get stuck. But you would need to educate your self on Formula Queries first.Or contact me directly by the email in my signature line if you want to take this off the Forum.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

Counting of Rows on Same Values