Display field if certain criteria are met 1

[meghan95]

I'm trying to develop a report using data imported from Outlook based software. The report will have a list of companies down the page with various related data (# of employees, description, industry, etc.) across. In the last column for each company I want to include the name of the company's attorney, if they have one. If not, just leave the field blank.
To complicate matters, the field that contains the attorney criteria includes multiple other items separated by a semi-colon (attorney; accountant; investment banker) due to the import from Outlook.
Using an IIf function, I've managed to get it so that the attorney's name would show up if he was related to the ABC Company, but if there are numerous people associated with this company, it returns ABC Company 3 or 4 times in a row with the attorney's name in the first entry and then blank fields for the others.
If I query just for the "*attorney*" part of the field, it only returns companies that have an attorney, but I need all companies residing in my table in the report.
Any thoughts would be greatly appreciated!

 

[shannonp]

The relationship here then is either N:M or M:M. This means that what you are receiving is correct...

Let me ask you a question, Can a company have more than one attorney? If you answered YES then how do you know which attorney to append to which company?

One solution would be to add a column to the 'Attorney's Table' called MAIN_COMPANY and have which company they affiliate to most. Then in your query simply relate it to the existing 'Company Field'.

Hope that makes SOME sense.

 

[shannonp]

Also, you could consolidate down and group the attorney information to just the 'company name' and 'attorney'.

 

[meghan95]

I can assume that each company only has one attorney. How would I go about consolidating down? I'm pretty new at all this stuff, so bear with me!
Thanks

 

[shannonp]

OK

Before I can help further, you're going to have to eleborate on how your data is stored (tables + fields).

 

[meghan95]

There are 2 tables with the following fields:
Table 1-Company, Industry, Description
Table 2-Company, Contact Name, Contact Role

The Contact Role field contains many criteria, separated by semi-colons. For example, one person could be all of the following: (co-investor;attorney;referred by).

The report I want to write is set up with these headings:
(Company Name,Industry,Description,Name of Attorney-if Applicable)
I only want each company to occupy one row in the report.

Not all companies will have attornies, and I can assume that each company has no more than one.

I hope that makes a little more sense. Thanks for your help.

 

[shannonp]

Maybe try this query:

SELECT Table1.[Company Name], Table1.Industry, Table1.Description, IIf([Contact Role] Like "*attorney*",[Contact Name],"&quot AS [Name of Attorney]
FROM Table1 INNER JOIN Table2 ON Table1.[Company Name] = Table2.[Company Name];

Let me know if you need clarification

 

[meghan95]

Thanks - this gave me just the name I needed in that field.