Counting records

[rgaetos]

I have a stepped report the list volunteer names and breaksdown the volunteer activies and hours that they have participated in. I am trying to do a count of how many volunteers are in the report, but if I do
Count([VolunteerID]) it counts all the activities rather than just how many volunteers there are. Any suggestion or solutions?

 

[Malcy]

Hi
Its not perhaps the neatest but if you set up a temporary table with simply the VolunteerID field (lngVolId) and set it to a key index (so there are no duplicates allowed). Lets call it tblTmpVolunteers
Then put together two queries, one called qdelTv which is simply a delete query to clear down any previous data in the table (so you get an accurate record), then another qappTv which appends all records from your current query to the tblTmpVolunteers - but will actually only add unique Volunteer ID records.
Then you do a DCount on lngVolId in tblTmpVolunteers.

So as a code sequence it would look something like

 Dim strQryDef as string
DoCmd.SetWarnings False
strQryDef = "qdelTv"
DoCmd.OpenQuery strQryDef
DoCmd.SetWarnings True

DoCmd.SetWarnings False
strQryDef = "qappTv"
DoCmd.OpenQuery strQryDef
DoCmd.SetWarnings True

Then your Volunteer count would be DCount("lngVolId", "tblTmpVolunteers"

Hope this is of some help. Even if you don't use it is may stimulate a better solution
Good luck

 

[kate10123]

I have a very similar problem this this.

I am trying to use the DCOUNT function to count the number of students that have attended a tutorial

Here is what I have:

=DCount("StudentID","Tutorial Information")

How can I amend this to only count each StudentID once? the above formula counts 7 records but there should only be 1 because each of these tutorials was attended by only one Student. (This studentID appears 7 times in the query due to unique ID being the tutorialNo)

 

[dkinley]

For both posters ....

DCount([FieldNameToCheck],"[TableorQueryName]", _
     "[OneFieldName] = " & SomeParameter & " AND [SecondFieldname] = " & SomeOtherParameter)

Important bit is the AND. Also, will need to apply appropriate syntax for string/number/date.

-dK

 

[Malcy]

The above works because have created a new table where there are unique records so an unqualified DCOUNT will work.
In your example I think you would need to qualify such as

=DCount("StudentID","Tutorial Information", "TutorialNo = " 1)

But you would need to play with that syntax on the qualifying bit since I might not have the inverted commas in the right place
HTH

 

[MSAccessRookie]

I see a coding solution and a dcount soultion, but what I do not see is the answer that I would have given, so Here it is.

When I have multiple records with similar Ids, and want to count the number of IDs, not the number of occurrances of the IDs, I use a DISTINCT qualifier in the select statement (Substitute your field/table names as required)

Select Count(DISTINCT(VolunteerName))
From TheTable
Group By VolunteerName
Order By VolunteerName;

 

[kate10123]

Thanks for all the replies but I am having a bit of trouble knowing where to put the code.

I am using an unbound textbox to display the total and have tried putting the =DCOUNT in it but this doesn't work because I have no fields in my table to use any criteria.

The SELECT DISTINCT looks like a good alternative but how I can I put that behind the textbox? How can it be declared in VB?

I can't change the query as the query is used to display data in a listbox and these textboxes are snapshot stats of this query.

 

[MSAccessRookie]

I have a very similar problem this this.

I am trying to use the DCOUNT function to count the number of students that have attended a tutorial

Here is what I have:

=DCount("StudentID","Tutorial Information")

How can I amend this to only count each StudentID once? the above formula counts 7 records but there should only be 1 because each of these tutorials was attended by only one Student. (This studentID appears 7 times in the query due to unique ID being the tutorialNo)

Wouldn't it be possible to base the DCOUNT on a query that uses a Select DISTINCT as part of its coding?

 

[dkinley]

I think you are using a query, so posting this for Kate in case she doesn't have any records to return since it seems she is using code.

Although Rookie's idea makes good sense instead of doing an endless string of multiple parameters.

Note: If DCount (or any other domain aggregate functions) do not find anything, they return a Null.

So ... to modify a multiple parameter DCount...

If Nz(DCount([FieldNameToCheck],"[TableorQueryName]", _
     "[OneFieldName] = " & SomeParameter & " AND [SecondFieldname] = " & SomeOtherParameter),0) = 0 Then 
     'There are no records so do what you do in this instance
Else
     'There are records so do what you want to do in this instance
End If

-dK

 

[kate10123]

As I have no parameters on which to base the DCount on, I am trying the query approach.

I have used the following:

SELECT DISTINCT Count(tbl_Tutorial.StudentID) AS CountOfStudentID
FROM tbl_Tutorial;

This however still returns 8 and yet there is only one StudentID that is appearing 8 times.

 

[MSAccessRookie]

As I have no parameters on which to base the DCount on, I am trying the query approach.

I have used the following:

SELECT DISTINCT Count(tbl_Tutorial.StudentID) AS CountOfStudentID
FROM tbl_Tutorial;

This however still returns 8 and yet there is only one StudentID that is appearing 8 times.

Try it this way:

SELECT Count([B][COLOR=red]DISTINCT[/COLOR][COLOR=red]([/COLOR][/B]tbl_Tutorial.StudentID[B][COLOR=red])[/COLOR][/B]) AS CountOfStudentID
FROM tbl_Tutorial;

Note: I cannot remember of the parenthesis (in red) are required or redundant.

 

[kate10123]

Thanks but that didn't work either as Access doesn't support that method.

Used this instead:

SELECT Count(*) AS Students
FROM
(SELECT DISTINCT StudentID FROM tbl_Tutorial) AS T;

Thanks anyway