Count unique records in query ercjfk 27.07.2006 19:30:02 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?

RE: Count unique records in query Jeff C 27.07.2006 19:46:02 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. -- Jeff C Live Well .. Be Happy In All You Do "ercjfk" wrote: [Quoted Text] > 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?

RE: Count unique records in query ercjfk 27.07.2006 20:00:02 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. "Jeff C" wrote: [Quoted Text] > 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. > -- > Jeff C > Live Well .. Be Happy In All You Do > > > "ercjfk" wrote: > > > 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?

Re: Count unique records in query "Mikal via AccessMonster.com" <u16180[ at ]uwe> 27.07.2006 20:55:49 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 ercjfk wrote: [Quoted Text] >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? -- "We have met the enemy and he is us." -- Pogo Possum Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1

Re: Count unique records in query "John Spencer" <spencer[ at ]chpdm.edu> 28.07.2006 12:35:58 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) "ercjfk" <ercjfk[ at ]discussions.microsoft.com> wrote in message news:5F2048EC-C460-4E7A-A078-4EA63FB2058C[ at ]microsoft.com... [Quoted Text] > 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. > > > "Jeff C" wrote: > >> 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. >> -- >> Jeff C >> Live Well .. Be Happy In All You Do >> >> >> "ercjfk" wrote: >> >> > 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?