too many repetitions (1 Viewer)

[shafh]

Hi,
I am having problems with a report I’m working on. In my report I am getting data from 3 tables; “IndustryList”, “CompanyList” and “IndustryPositives”.
From the IndustryList I am getting the IndustryName field. This consists of 41 different Industry names such as Healthcare or Retail-Discounters.
From the CompanyList I am getting the names of companies. I have 910 companies; each of them falls into only one of the 41 Industries.
From IndustryPositives I am getting the Positive aspects of each industry, again this is unique and has 41 entries, one per IndustryName.

My problem is this. When I create this report I group by IndustryName and thus get 41 entries. Within each IndustryName I get the names of all the Companies that fall in that Industry. However the IndustryPositives gets repeated 910 times, once for each CompanyName. So I end up with 910 IndustryPositives even though I only want it once per Industry.
Somehow I want the names of all the companies within an Industry and the IndustryPositives only ONCE per Industry in my report.

Thanks a lot, I realize that was a long question!

p.s. the Hide Duplicates option doesn't really work because the IndustryPositives still appear once every new page since sometimes the company names flow over to the next page.

 

[AncientOne]

Sounds like your underlying query isn't properly set up. We need to look at that first.

 

[shafh]

query

Here is the RecordSource:

SELECT [IndustryList].[Industry], [CompanyList].[CompanyName], [IndustryPositives].[Positives]
FROM (IndustryList LEFT JOIN IndustryPositives ON [IndustryList].[Industry]=[IndustryPositives].[Industry]) LEFT JOIN CompanyList ON [IndustryList].[Industry]=[CompanyList].[CompanyIndustry]
ORDER BY [IndustryList].[Industry], [CompanyList].[CompanyName];

Thanks!

 

[shafh]

Thank you

I was able to figure it out.
Thanks