Grouping Problem

[Former Member]

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.

[raghavendra_hullur · Accepted solution]

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

[Former Member · Accepted solution]

Hi,

• I have sub pay item values with a pay item detail but not work item since they are used directly from sub pay item table.
• I have the sub pay items with their respective pay item detail,leaving the work items having no pay item and sub pay item details with the following join and result.

• It raises an error saying Database Connection error:If tables are already linked then the join type can not change.

Any thoughts on that?

[raghavendra_hullur · Accepted solution]

Hi,

If you have Pay Item Details for each Sub Pay Item, then those should be displayed as second group as per your grouping irrespective of whether you have Work Item value. But those are not displayed, so there might be a problem with joins you have created between tables.

As for as your error message concerned, that happens when you have tables with circular links. That is, Table A, Table B and Table C are joined based on same column.

Check these links for more info:

Thanks,

Raghavendra

[Former Member · Accepted solution]

I can't seem to figure out how to join the tables to get the right result frankly. I tried writing the SQL on the database first but same result gets displayed.

I haven't gone through this issue before,can you help me on that?

Thank you

[raghavendra_hullur · Accepted solution]

Can you share sample data for all columns for all 5 tables?

What are the outer joins you have currently used?

Also, if you are allowed, please share the report with Saved Data.

Thanks,

Raghavendra

[Former Member · Accepted solution]

Project id	project name	overhead	profit margin
po1	sample project	10	15

pay item	description
1400	Office, Laboratory, Accommodation
6400	Bituminous Road Bases and Surfacing
8100	Foundations for Structures
9400	Road Signs

pay item	sub pay item	description	unit
1400	14.03	Items measured and paid for by lump sum
1400	14.04	Services
1400	14.05	Vehicle for Engineer and his staff
6400	64.05	Base layer construction
8100	81.01	Site Investigations	Lsum
8100	81.32	Cast in situ concrete	m3
8100	81.05	Back fill to excavations

pay item	sub pay item	work item	description	unit
1400	14.03	14.03( i )	Office equipment	Lsum
1400	14.04	14.04 (a)	Fixed Costs	Lsum
1400	14.05	14.05 (a)	Vehicle Type A	no.
6400	64.05	64.05 (a)	60/70 penetration grade bitumen	ton
8100	81.05	81.05 (b)	Imported material	m3

project id	work item
p01	14.03( i )
p01	14.04 (a)
p01	14.05 (a)
p01	64.05 (a)
p01	81.01
p01	81.32
p01	81.05 (b)

I don't know how to share the report with a saved data. How can i attach?

[raghavendra_hullur · Accepted solution]

Hi,

I have replied to your email with changes at report level.

Please check and confirm.

When all the required columns have data without any NULL values, can you tell me why you still want to go for outer joins?

Any specific requirement?

Thanks,

Raghavendra

[Former Member · Accepted solution]

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.