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
BBluAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BBluAuthor Commented:
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.
0
Patrick MatthewsCommented:
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
0
Patrick MatthewsCommented:
Sorry!


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


or:

SELECT t1.Initials, Count(t2.T_Initials) AS TheCount
FROM Inititials t1 LEFT JOIN
    [T_Initials (SHARED)] t2 ON t1.Initials = t2.Initials
GROUP BY t1.Initials
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BBluAuthor Commented:
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] & "'")
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bad example.  Dcount() (nor any of the domain functions) should be used in a query.
<<But I still don't understand how/where "UnitPrice" is relevant.>>
  UnitPrice is just a named field that is being counted.  You could use just about any field in the record for this (you just need to watch out for using a field that might have nulls in it).
 Instead of using DCount(), there are a number of ways to get a count from a related table.
 Your best option is a GroupBy query, which has the two tables joined on the Initials fields.  Then group on the initials, return last or first on the fields you need as appropriate, and select Count() on one of the fields.
JimD.
0
BBluAuthor Commented:
Thanks, Guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.