Query: How many times does a value exist?

[hognabbt]

Hello,

I have a calculation issue that I have not figured out how to do. Basically: "In how many records does a specific value appear in another table"? Here is an example how I would use it:

Table: tblPersons
Fields: Name (Primary key), BirthDate, WorkerID,

John, 20.02.1950, 12345
Fred, 21.02.1951, 12346
Mike, 23.02.1952, 12347
Larry, 24.02.1955, 12348
Paul, 24.02.1944, 12349

Table: tblPlaces
Fields: Place (Primary key), HostName

Warehouse, John
Factory, John
Factory2, Fred
Cafe, Mike
Shop, Paul

I'd need to create a counting field to a query and the query would look like this:

Query
Fields: tblPersons.[Name], "HostingTotallyPlaces"

John, 2
Fred, 1
Mike, 1
Larry, 0
Paul, 1

Any ideas how to solve this? I tried to work with SQL Count command but it didn't solve the issue completely. Thanks in advance!

 

[dcb]

I tried to work with SQL Count command but it didn't solve the issue completely

Looks like you were on the right track why do you say "completely"? Is it the larry,0?

 

[hognabbt]

Looks like you were on the right track why do you say "completely"? Is it the larry,0?

Well, not really. I tried to create a SQL query with Count(DISTINCT), but I didn't manage to do a proper statement.

 

[Brianwarnock]

Have you actually got anywhere? Could you not have posted your SQL and result?
The Count of the hosts is a simple Totals query Grouping on name and using Count(*), however it would not pick up names with no entries in Tblplaces, , hence DCB's question about Larry.
To get that you need to outerjoin tblPersons to the query and use Nz on the count field to convert the Null to 0.
There have been several posts on this lately.

Brian