Count unique records in query

E

ercjfk

I've created a query that lists the student ids for students who were
enrolled in particular courses (along with some other data). However, some
students are enrolled in more than one course so the query contains some
dulicate student ids. I want to get rid of the duplicated student ids so that
each student appears in the list just once.

I tried creating another query to count the values in the column, but I
don't have the option to select "Count Distinct" in the totals row as the
Access Help suggested. Any ideas?
 
J

Jeff C

You could try to Normalze your data. In other words it sounds like you have
one big table with duplicate information in it.

Put your table into a query and select just the students and their IDs, in
the properties for the query select "Unique Records" = YES

Then turn this into a make table query, you will then have a table made up
of just the students. Open the tqable in design mode and on the field for
Student ID make in indexed no duplicate and make it your primary key.

Open up your Relationship Window and put your two tables into it, relate
them on the Student ID.
 
E

ercjfk

It isn't that I have duplicate information in my table. I created a query
that pulled lists of students who were enrolled in 5 different classes. None
of the records are exactly alike in the query, but some students were
enrolled in more than one of the courses so their student ids appear multiple
times in the query. I now want to count the number of unique student ids in
the query.
 
M

Mikal via AccessMonster.com

Go to the menu bar and choose View | SQL view. This will reveal the SQL code
behind the query. Locate the word SELECT (probably the very first one).
then type DISTINCT after the word SELECT. Then see if you can run the query.

Mike
 
J

John Spencer

It would help if you posted your original query. (Hint - View: SQL on the
menu)

To get the count you need a distinct query that will return the just the
StudentIDs and then you can run a count against that query.
A generic SQL statement for that would look something like the following.

SELECT Count(*)
FROM
(SELECT Distinct StudentID
FROM Yourquery)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top