Skip to Content
avatar image
Former Member

Grouping Problem

I have some issues in groping my records.My grouping is based on four tables respectively, namely Project,Pay_Item,Sub_Pay_Item and Project_Work_Item. Project_Work_Item holds a Project and items from Work_Item and Sub_Pay_item used for a project.

Here's the format:

-Project A------[based on project id]                                                       -Project B------[based on project id]

     -1200------[based on pay item]                                                            -1400------[based on pay item]                                                               

          -12.01------[based on sub pay item]                                                  -14.01------[based on sub pay item]

               -12.01-(a)------[based on work item in Project Work Item]                    -14.01-(a)-------[based on work item in Project Work Item]

I Joined the tables using right join and i get results for the work items. I also wanted to view the result for the sub pay item since it can be used for a project.   

Here's the format i want together with the work items

-Project A------[based on project id]                                                      

     -1200------[based on pay item]                                                        

          -12.01------[based on sub pay item] 

              -sometimes there may not be a work item here

               since pay item can be used directly.

1.png (15.3 kB)
2.png (27.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 26, 2014 at 08:40 AM

    Hi,

    From your example, it's visible that you have records with sub pay item values without pay item and work item details.

    Check by creating a sample report with just pay item and sub pay item tables with an outer join on pay item column, so that you can confirm whether there are any sub pay items without having pay item detail.

    Also, there is no join between Pay Item and Sub Pay Item tables in the screenshot you have shared. Isn't that required?

    Thanks,

    Raghavendra

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Raghavendra Hullur

      Hi Raghavendra,

      Items are used from two tables for a project. I think you haven't noticed that items 81.01 and 81.02 from sub pay item table were used for the project.

      The only reason I'm using them is that they have a unit,which are few. I can move them to the work item table and use a proper join instead.