Grouping data

[Former Member]

I have the following data

Table 1:

Company_id Company_name Location Website

Id 1 C1 L1 W1

id 2 C2 L2 W2

Table 2:

Company_id Contact_per_name Email_id

id1 Person1 E1

id1 Person2 E2

Now in my reprot i have created a command where i joined two tbales (Left join) and i got the below data,

id1 C1 L1 W1 Person1 E1

id1 C1 L1 W1 Person2 E2

id2 C2 L2 W2

Now i need to display the report with each company details in each page,

First page:

Company Name:C1

Location:L1 Website: W1

Contact person Email id

person1 E1

Person 2 E2

Second page:

Compnay name: C2

Location:L2 Website:W1

I have created a company name group and placed the location, website, contact person detials in the details section. If Contact person does not exist for a company, it is displaying correctly. But when the company is having more than one contact person details, location & website is getting displayed twice. How to group the report?

Thank you.

[Former Member · Accepted solution]

Looks like you need to relate person to both a company and location.

At present you only appear to be linking to Company ID, thus if you have two people and two locations you get a catesion joinn

Check to see if there is a location code in your table 2 and join that to location Id on table 1.

Ian

[Former Member · Accepted solution]

no need to group contact persons with location, A company can have any number of contact persons. Now i am able to display the detilas. I have displayed the company name,location, website in page header and created a group (company name) and checked "New page after each visible group" and placed the contact person details in details. Now i am able to display the required format.

Thank you so much for the reply.

[Former Member · Accepted solution]

I have one doubt, If a company does not have contact persons details, how to suppress the group header & detials section?

[Former Member · Accepted solution]

Dont use a left join, then you will only see companies with employees.

Ian

[Former Member · Accepted solution]

I am able to see all the company and employee detials. I didnt get your point.

[Former Member · Accepted solution]

I thought you did not want to see companies with out employees. Using an equal join between company and employee table will automatically suppress the companies without employees.

If you want to manually supress then go to section expert and enter

isnull({table2.employeeid})

Ian

[Former Member · Accepted solution]

Thank you so much..