Counting unique records

[jj]

I have created a database of students who are attending our summer program.
I have a form, based on the table of applicants, that has a subform attached
where staff members can enter comments. The subform is linked to it's own
table. I would like to count the number of applicants that I have comments
on. If I use the =Count(*), it counts every record. I have set up a query
that counts the unique field, student ID, in the table. How can I count the
number of unique student IDs that are in the subform table in a report?

 

[strive4peace]

Hi jj,

in a form or report, put this in the footer:
=Sum( IIF(IsNull([comment_controlname]),0,1)

WHERE
comment_controlname is the Name property of the control that holds the
comment

In a query:
field --> NumComment: dSum("[studentID]","[Tablename]", "Not
IsNull([comment_fieldname])")

WHERE
studentID is the name of your student id fieldname
Tablename is the name of your table
comment_fieldname is the name of your comment fieldname

Warm Regards,
Crystal

*
have an awesome day
*

 

[Jackie L]

Create a query (qryCountStudents) with the Comment table. Bring down the
Student Id field and View - Totals. Keep Group by for that field.

Now, on your report you can create a field with the Control Source of:
=DCount("StudentID","qryCountStudents")

Hope this helps,
Jackie

 

[KARL DEWEY]

Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[Comment]
WHERE ((([Comments].[Comment]) Is Null));

 

[KARL DEWEY]

Corrected the join ---
Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[StudentID]
WHERE ((([Comments].[StudentID]) Is Null));

--
KARL DEWEY
Build a little - Test a little

Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[Comment]
WHERE ((([Comments].[Comment]) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have created a database of students who are attending our summer program.
I have a form, based on the table of applicants, that has a subform attached
where staff members can enter comments. The subform is linked to it's own
table. I would like to count the number of applicants that I have comments
on. If I use the =Count(*), it counts every record. I have set up a query
that counts the unique field, student ID, in the table. How can I count the
number of unique student IDs that are in the subform table in a report?

 

[Douglas J. Steele]

I don't think so, Karl. Count([StudentID]) is going to return exactly the
same as Count(*).

Try:

SELECT Count([StudentID]) AS CountOfStudentID
FROM
(SELECT DISTINCT [table of applicants].StudentID FROM
[table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[StudentID]
WHERE [Comments].[StudentID]) Is Null)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Corrected the join ---
Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[StudentID]
WHERE ((([Comments].[StudentID]) Is Null));

--
KARL DEWEY
Build a little - Test a little

Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[Comment]
WHERE ((([Comments].[Comment]) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have created a database of students who are attending our summer
program.
I have a form, based on the table of applicants, that has a subform
attached
where staff members can enter comments. The subform is linked to it's
own
table. I would like to count the number of applicants that I have
comments
on. If I use the =Count(*), it counts every record. I have set up a
query
that counts the unique field, student ID, in the table. How can I
count the
number of unique student IDs that are in the subform table in a report?

 

[jj]

Thank you. This was simple enough for me and I understand what is actually
happening.

 

[jj]

I'm testing this and it seems to work. Thanks for your help.
--
jj Trying to learn

Hi jj,

in a form or report, put this in the footer:
=Sum( IIF(IsNull([comment_controlname]),0,1)

WHERE
comment_controlname is the Name property of the control that holds the
comment

In a query:
field --> NumComment: dSum("[studentID]","[Tablename]", "Not
IsNull([comment_fieldname])")

WHERE
studentID is the name of your student id fieldname
Tablename is the name of your table
comment_fieldname is the name of your comment fieldname

Warm Regards,
Crystal

*
have an awesome day
*

I have created a database of students who are attending our summer program.
I have a form, based on the table of applicants, that has a subform attached
where staff members can enter comments. The subform is linked to it's own
table. I would like to count the number of applicants that I have comments
on. If I use the =Count(*), it counts every record. I have set up a query
that counts the unique field, student ID, in the table. How can I count the
number of unique student IDs that are in the subform table in a report?

 

[strive4peace]

you're welcome, jj happy to help


Warm Regards,
Crystal

*
have an awesome day
*

 

[Pieter Wijnen]

Sometimes I miss Oracles Count(Distinct .. )

;-) Pieter

I don't think so, Karl. Count([StudentID]) is going to return exactly the
same as Count(*).

Try:

SELECT Count([StudentID]) AS CountOfStudentID
FROM
(SELECT DISTINCT [table of applicants].StudentID FROM
[table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[StudentID]
WHERE [Comments].[StudentID]) Is Null)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Corrected the join ---
Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[StudentID]
WHERE ((([Comments].[StudentID]) Is Null));

--
KARL DEWEY
Build a little - Test a little

Try this editing it to your actual table and field names ---
SELECT Count([StudentID]) AS CountOfStudentID
FROM [table of applicants] LEFT JOIN [Comments] ON [table of
applicants].[StudentID] = [Comments].[Comment]
WHERE ((([Comments].[Comment]) Is Null));

--
KARL DEWEY
Build a little - Test a little


:

I have created a database of students who are attending our summer
program.
I have a form, based on the table of applicants, that has a subform
attached
where staff members can enter comments. The subform is linked to it's
own
table. I would like to count the number of applicants that I have
comments
on. If I use the =Count(*), it counts every record. I have set up a
query
that counts the unique field, student ID, in the table. How can I
count the
number of unique student IDs that are in the subform table in a
report?