Count values in a column on a table

[bradp1979]

Seems simple enough, but I want a query that will return a value of how many times a customer ID is found in the "reference" column.

So basically, I'm looking to write a query that is = Count the number of times this client's ID is found in the Reference column of tblClient.

I just don't know the right syntax to do that.

[bradp1979]

I tried both =Count([“Reference”],[”tblClient”],[ClientID]) and =DCount([“Reference”],[”tblClient”],[ClientID]), but I got #Name? as an error message.

[June7]

Count(*)

DCount("*","tblClient","Reference=" & [ClientID])

Brackets [] if used would be within the quote marks.

[bradp1979]

When I put =DCount("*","tblClient","Reference=" & [ClientID]) in the control source, I'm getting #Error.

Why can't anything with this Frankenstein DB be easy?

[June7]

I don't know your db so can't say.

If you want to provide for analysis, follow instructions at bottom of my post.

[bradp1979]

I've provided it a few times, and, due to the errors (I had to delete far too much of the DB), you weren't able to do much with it.

[June7]

Oh, that one.

The DCount() syntax provided is correct.

That expression is in textbox on form? Is ClientID a field in the form RecordSource? Is it a text or number type?

[ssanfu]

First you have

but I want a query that will return a value of how many times a customer ID is found in the "reference" column.

then you say "control source"....

When I put =DCount("*","tblClient","Reference=" & [ClientID]) in the control source, I'm getting #Error.

Confusing....Which one do you want?

Look at post #3 again.
June's example is the syntax for a query and does not have an equal sign! In a query, do not use the equal sign.
In the query, it should look something like:

ClientCount: DCount("*","tblClient","Reference=" & [ClientID])

If you use an equal sign, that is for the control source for a control on a form.

= DCount("*","tblClient","Reference=" & [ClientID])

Plain as mud, right???


EDIT:
I just looked at the table and the field "Reference" is a TEXT type field. That requires delimiters.
So the syntax for a query

ClientCount: DCount("*","tblClient","Reference = '" & [ClientID] & "'")

and a control

= DCount("*","tblClient","Reference = '" & [ClientID] & "'")

[bradp1979]

Ok, Somewhat clearer than mud.

I initially wanted to do a query, but then I realized it wasn't necessary.

I put a textbox bound to ClientID on frmClient and it works fine: pulling the ClientID from tblClient. ClientID is a field on tblClient and the type is AutoNumber. Reference is also a field on tblClient, its type is Text.

I put a new textbox directly underneath the textbox that is bound to ClientID, and for its control source, I have =DCount("*","tblClient","Reference=" & [ClientID]).

As per Ssanfu's suggestion, I changed it to =DCount("*","tblClient","Reference = '" & [ClientID] & "'"), and Voila!

Thank you yet again, Ssanfu and June7. You two are proving invaluable.

Now for the tricky part: I'd like to double click on the number that results from the code in the textbox, and have it show which specific clients were referred by this particular client.

[June7]

I am confused and baffled. Why would there be two fields in tblClient with same data? Why does Reference have client ID and ClientID also has client ID? Are there really two tables involved?

A textbox cannot be bound to a field and also have an expression in ControlSource.

[bradp1979]

One table is involved: tblClient. 2 fields are involved: ClientID and Reference. I used the wrong verbiage. The textbox has an expression, and is not bound to anything.

I apologize for the confusion.

[bradp1979]

Ok, what I'm trying to do, is create a new query that will look at tblClient in the Reference field, find all instances of the ClientID that's in the ClientID box on frmClient, and display the names and ClientIDs of each client that the specific client referred.

I started making it, then confused myself.

[bradp1979]

To put a picture to what I'm doing, see that in the picture below, I have client ID, which is taken form tblClient. Clients referred, which is derived by =DCount("*","tblClient","Reference = '" & [ClientID] & "'") as the control source.

For this particular client, they referred 14 clients. In the box below, I want to see which specific clients this client referred, and how many events they've had.

I know a query can do this, but I don't quite know how to make it happen.

[bradp1979]

Hopefully the errors you encountered previously won't affect this particular project.

[June7]

Two tables not found in relinking - tblEmployee_bu and tblInsCert. No errors now.

Looking at tblClient, understand now. ClientID 11150 has Reference of 3396. What does 3396 mean? Did you delete ClientID record 3396? So I should put some legitimate values into Reference field? I presume not all clients will have referral from another client.

What form do you want to do the count on and which textbox?

If you want to be able to do a self-join of tblClients so the client info for Reference can be viewed (as might be desired in a report), Reference will have to be a number field.