asked on

dcount in query

In a query, I'm trying to count the number of occurences of a field in the query in another table. So, my query has two tables and one of the columns is [Initials]. I'm trying to look in another table, T_Initials (SHARED), for the number of times the value in Initials occurs in the column/field with the same name.

I know the syntax, but it seems I only need two parameters: the table I'm looking in..and the criteria, in this case: [Initials](from the other table)=[T_Initials (SHARED)].[initials] (from the current query)

Can someone help me with this expressions

BBlu

ASKER

I got this example from:
http://www.techonthenet.com/access/functions/domain/dcount.php

DCount("UnitPrice", "Order Details", "OrderID = 10248")

In this example, you would return the number of records in the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:

But I still don't understand how/where "UnitPrice" is relevant.

Patrick Matthews

Avoid DCount unless there is no other way :)

SELECT Count(t2.T_Initials) AS TheCount
FROM Inititials t1 INNER JOIN
[T_Initials (SHARED)] t2 ON t1.Initials = t2.Initials

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

Thanks, Matthew. I can't figure out a way around it...I used this and it worked:

DCount("Initials","T_Initials (SHARED)","Initials= '" & [T_Initials (SHARED)].[Initials] & "'")

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

Thanks, Guys!