Hi,

I have two related tables, News and Companies. News table has a field, which lists all companies related to a certain news. I have a query based on company name and results show all news related to this company.

The problem is that I would need to create a report based on that query that lists all companies shown in the results related company field and calculate how many times they are shown. This would help to see which companies do the most co-operation with each other.

I hope this wasn't explained too poorly..

Have you tried the Count Function?

I have tried it in the report, but it doesn't count how many times certain company shows up in the query results or on the report. Or then I am doing it wrong.. I'm still quite new in the Access world.

Just to clarify a little bit, my original tables have fields like:

News-table:
ID
date
headline
text
related companies (For example Ford, Opel and BMW)

Company-table:
ID
Company name
Country
Address
Info

So is it possible to count how many times for example Opel shows up in news that also have BMW in their related companies -field?

If all you want to see is a Count per company per news then you can create a Totals Query and group by News, followed by Company. Then count the Company by selecting Count. Here's a link on how to count using a query:

http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx?CTT=3

You can then use that query as the Record Source of your report (i.e. if you wanted a report in addition).

Or if you want to still see all the records but be able to see a count per group (i.e. Company) then you need to create a Company group using Sorting and Grouping, then drop a textbox in the group footer and put this as the control source =Count(*)

Thank you for your quick response! It actually helped me to the right direction, but I still have some issues..

I'm sorry I haven't been able to be more clear about my goals. I'll try again. My ultimate goal is to make report with layout something like this:

Company information:
Name: (for example Opel)
Address:
Info:
_______________________________________

10 latest news regarding company:

_______________________________________
Companies related to current company:
Company name: Times appeared in news with current company:

(For example
BMW 7
Ford 3)
_____________________________________

In that case all you need is a DCount() function.

http://www.techonthenet.com/access/functions/domain/dcount.php

Use that in the control source of the textbox.

Thanks again. I've been playing around with DCount, but I just cant get it to work. In which part of the report should it be? Footer? And which criteria if any should I give to it, since I want all the companies calculated that show up in results? Sorry you have to walk me through this this far :)

You put it in the section that requires the count. As mentioned, in the textbox.

What expression have you written that isn't working?

Well I have pretty much tried every expression using "Related companies", "News", "Company" "COmpany name" and query results . Also I have tried different criteria. I feel really daft for not getting how this works..

I attached the report design view that I'm using. The Dcount (as it happened to be at the moment) is in the page footer.

An equal to (=) should precede the DCount function and if the field name has spaces, enclose it in square brackets as well "[Related Companies]"

I tried
=DCount("[Related companies]","News")
and got an error:
"The expression you entered contains invalid syntax."

This database is not designed by me and I just realized that Related companies are stored as numbers which relate to companys ID number on the Company table. Does this matter at all?

I really appreciate your help by the way!