DCount in query

[Leo_Polla_Psemata]

Hi
I have a query (say DCT) in which one field outlines info (say FirstName)
This FirstName field might outline some data twice.

Example

Nick
Joan
Mick
Chak
Joan
Char
Matt
Nick

I would like to make a field which will count the numbers that the FirstName appears in this query and it would be like this


Nick 2
Joan 2
Mick 1
Chak 1
Joan 2
Char 1
Matt 1
Nick 2

Is this easy ?
I cannot use this general syntax because the domain is the same query i try to run
DCount ( expression, domain, [criteria] )

 

[Ranman256]

you dont use Dcount in a query, the query IS the count.
turn on Grouping Totals (summation sign) and set the count property

 

[Leo_Polla_Psemata]

It doesn't work because, besides FirstName field, there are several other fields and the field combination make the entire record as unique so the "count" shows always "1"

 

[Minty]

Use a sub query http://allenbrowne.com/subquery-01.html to get the count and bring that into your query results

 

[plog]

Do you need those other fields in your query?

 

[Leo_Polla_Psemata]

Yes I do need the other fields in the query.
I made a sub query as per Minty's suggestion and it works.
However I was thinking that for such a "simple" requirement
there should be some easier method.

 

[plog]

Just because it can be simply stated (e.g Land a man on the moon) doesn't mean the implementation is going to be. And to be fair, a sub-query really isn't that complicated. The requirement of showing all the data from the initial table is what made the sub-query necessary--eliminate that and you can get your totals by Name in less than 30 characters of SQL.

 

[Leo_Polla_Psemata]

Yes, sure
sub query isn't that complicated, the problem is that i create too many objects which I don't remember later on what purpose do they serve, i have several obsolete objects which i am afraid to delete, last but not least, queries based on tables and sub queries, might be a cause of duplicate result in a report, you may have 1000 records but in the report you get 1025 records because one query joint properties were not set correctly.
You see, I am not expert in IT, I just find access very helpful tool and I have learned too many things from this site

 

[Minty]

Create the query in code on the form where it is used, then you have less chance of "mislaying" or changing the underlying source of the data.