• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

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
0
BBlu
Asked:
BBlu
  • 3
  • 2
2 Solutions
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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)PresidentCommented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now