Why won't this distinct query display distinct?

[fergman]

Set Rs = Server.CreateObject("ADODB.Recordset&quot
SQL = "SELECT DISTINCT deplist.department, deplist.course from depList"
Rs.Open SQL, deplist, 1,3

The query will display distinct if I remove deplist.course, but since I will actually need two more columns later including deplist.course I need to have the ability to display distinct records in more than one column.

 

[spazman]

If you are expecting deplist.department to be distinct with your SQL statement it wont becuase you've asked to give you distinct deplist.department AND deplist.course, so if your table looks like this
DEPARTMENT COURSE
HR 123
HR 345
IT 123
AC 123

you will get all three records becuase concantenated they are distinct.

I hope this helps let me know.

 

[fergman]

so to make everything distinct would I have to do a distinct in front of each one?

 

[spazman]

No that won't work, what you may have to do is get your RS from the table and then use an if then statement to make sure you don't duplicate, or create a view that does the distinct for you and query that view.

Let me know if you need anything else.

 

[Tarwn]

If we are talking about courses and the sample is:
HR 123
HR 345
IT 123
AC 123

Than I would consider receiving 4 rows back distinct because each one is a distinct class.

Could you be a little more specific at exactly what you don't want repeating?

-Tarwn Experts are only people who have realized how much they will never know about a language.
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[fergman]

sure, basically my results are ACCT 5 times or so for each class offered by ACCT, I only want the ACCT department to display once. that would go for the course number as well as there are multiple sections for each course.
for example,
ACCT 251 03
ACCT 251 04
ACCT 251 05

 

[Tarwn]

Ok, the root of this problem is actually in your database design, not your select statement. In order to do what your wanting you have three options:
1) Redesign your db (I'll hit this later) which will get you closer, but not exactly what you want.
2) Use Independant select statements for dept (ACCT), course number(251), class number (03/04/05)
3) Write one select statement, but keep track of current dept and course while your looping through:

Dim tDept, tCourse
Do Until rs.EOF
   If rs(&quot;department&quot;) <> tDept Then
      Response.Write &quot;Department: &quot; & rs(&quot;department&quot;) & &quot;<br>&quot;
      tDept = rs(&quot;department&quot;)
   End If

   If rs(&quot;course&quot;) <> tCourse Then
      Response.Write &quot;<div style='padding-left:1em;'>&quot; & rs(&quot;course&quot;) & &quot;</div>&quot;
      tCourse = rs(&quot;course&quot;)
   End If

   Response.Write &quot;<div style='padding-left:2em;'>&quot; & rs(&quot;class&quot;) & &quot;</div>&quot;

   rs.MoveNext
Loop

That should give you something like:
ACCT
101
01
03
05
251
03
04
05
BIO
151
01
02
03


Ok, so back to the db design. It appears your designing a school scheduler. In terms of school a department is pretty obvious, but a course and class are very differant.
A course has a department and numerical assignment, as well as a description. It does not have a teacher or a meeting time or a building. You can think of it as the base definition for classes to use.
A class is an instantiation of a course. If we have 5 classes for BIO 101 then they will all hae the same department, the same course number, the same description, etc But in this case they will also have a building, a meeting room, a teacher, a semester, and a meeting time (among many other possibilities, like seating, etc)

So here is an example set of records:

dept | crse | class | title | description | teacher | time
-----+------+-------+-------+-------------+---------+------
ACCT | 251  | 03    | Acco..| Intermedia..| Phillips| 1:30
ACCT | 251  | 04    | Acco..| Intermedia..| Brown   | 12:00
ACCT | 251  | 05    | Acco..| Intermedia..| Smith   | 9:00

I left off some important fields for space conservation.

Can you see what is happening? We are repeating several fields over and over. This is bad. What happens now is if you need to update the course description your actually updating it in multiple locations, this causes a hit on performance as well as opening the door for errors (descriptions not matching for two classes in same course, etc)

So what we want to do is move the redundant fields to their own table. Lets call it Course

So here are our two tables now:
Course:
dept
crse
title
description

Class:
class_number
teacher
time

We need some primary keys. Text keys are generally considered bad, so lets go with auto incrememnting integer id's (autonumber fields in access, seeded integers in most other db's)

Course:
course_id
dept
crse
title
description

Class:
class_id
course_id
class_number
teacher
time

Notice I added the course_id to class as well to define a relationship between course and class. In this case we want a one-to-many relationship. In other words for each course there can be 0 or more classes that belong to it.

Now lets put our above three records in our new tables:

Course Table:
course_id | dept | crse | title | description 
----------+------+------+-------+------------------------
1         | ACCT | 251  | Acco..| Intermediate accounting for...

Class Table:
class_id | course_id | class_number | teacher | time
---------+-----------+--------------+---------+--------
1        | 1         | 03           | phillips| 1:30
2        | 1         | 04           | brown   | 12:00
3        | 1         | 05           | smith   | 9:00

Now when we want just the courses we can select fro just the courses table, if you want just the classes than instead of getting all of the extra course info you can select directly from the class table. You still can't select all the dept, course, and class info in one select statement without getting duplicate dept's and courses, but thats because you'll never be able to do that unless there is only one class per course and one course per dept.
But we have cut down on the data duplication, communications duplication, and time wasted by the server sending you fields you don't need.
Example SQL:
"SELECT * FROM Course" 'just the course info
"SELECT * FROM Class" 'all the class info without course descs, etc
"SELECT * FROM Class WHERE course_id = " & Request.Form("course_id&quot 'form form on a previous page, dropdown select?
"SELECT * FROM Course, Class WHERE Course,course_id = Class.class_id" 'all the info you would get now if you did a Select * on your current db layout

Don't know if this will help or not, got kind of carried away.

-Tarwn Experts are only people who have realized how much they will never know about a language.
________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[fergman]

Whew! That was informative, fortunatly my database is not so complex, all I need to do is have the user select their department, display the appropriate courses, and then sections. They will basically just be reserving their textbooks for the class they are in. I just need to do that about 7 times.

I think from what you are saying, my best bet is going to be 3 individual queries instead of a select obtaining all the data.
That way I can do a distinct for each one.
The sum total of my app is:
the user selects their classes (usually 3-4), then the data is written to a different table.