Field only populated if it meets a criteria

[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!

 

[Ctrler]

1.) Make a query for only those companies that match the criteria "*attorney*". Include a field for the person's name.

2.) Link this query 1.) to the table containing all your companies using a 'join property' that includes all records from your table but only only those companies from query 1.) where the joined fields are equal. Include all fields of your table and the 'name' field from query 1.).

I understand you've got the pieces ready, just put them together.

Cheers, G

 

[meghan95]

I gave this a try. It's only giving me the companies that have an attorney rather than listing all companies. I'm not sure this is possible.