Gossamer Forum
Home : General : Databases and SQL :

Access Query and ASP ..please help!

Quote Reply
Access Query and ASP ..please help!
Please bear with me..its a little long..:)

I have 2 tables

table 1:courses
cid(primary key),coursename,coursedesc

table 2:students_courses
stucourses_id(primary key), cid(foreign key), stu_addedby

contents in table 1 are added by me to the Access database.
But contents in table 2 are added by students thru the web.

I made a Query in Access which shows cid and coursename from courses and stu_addedby from students_courses.


SELECT courses.cid, courses.coursename, students_courses.stu_addedby
FROM courses LEFT JOIN students_courses ON courses.cid = students_courses.cid;

but it shows several duplicate values like.

cid coursename stu_addedby
--------------------------------------
1 MATH1234 jane
1 MATH1234 greg
2 PSYC3421 jane
3 EDLD5643 monica
3 EDLD5643 jane




In my ASP page I would like to show distinct cid,coursename

My ASP page should look like this for User "greg"

Hi Greg!!Your courses are

COURSE NAME ADD/UPDATE
----------------------------
MATH1234 UPDATE
PSYC3421 ADD
EDLD5643 ADD

but it wud show duplicates like below

Hi Greg !! Your courses are

COURSE NAME ADD/UPDATE
----------------------------
MATH1234 UPDATE
MATH1234 UPDATE
PSYC3421 ADD
EDLD5643 ADD
EDLD5643 ADD


ASP code is

*********************************************
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td width="247"><%=(Recordset1.Fields.Item("coursename").Value)%></td>
<td width="497">
<table width="500" border="0" cellspacing="1" cellpadding="1">
<tr>
<td>
<% if (Recordset1.Fields.Item("cid").Value) <> " " AND Session("MM_Username") =

(Recordset1.Fields.Item("stu_addedby").Value) then %>
<p> UPDATE</p>
<%ELSE%>
<p> <A HREF="course_details.asp?<%= MM_keepURL & MM_joinChar(MM_keepURL) & "cid=" &

Recordset1.Fields.Item("cid").Value %>">ADD</A></p>
<%end if%>
</td>
</tr>
</table>
</td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
Recordset1.Close()
%>
*****************************************************

PLEASE HELP!! I HAVE BEEN WORKING ON THIS FOR SEVERAL DAYS!! PLEASE help !
Thanks.
Quote Reply
Re: [suezee] Access Query and ASP ..please help! In reply to
use DISTINCT for the CID (fk) in the Student table.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] Access Query and ASP ..please help! In reply to
Hi Me,

I tried DISTINCT too..but it still shows duplicates...Frown

-Zee
Quote Reply
Re: [suezee] Access Query and ASP ..please help! In reply to
DISTINCT on which column? Might help if you priovide us with the SQL statement you tried.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] Access Query and ASP ..please help! In reply to
SELECT DISTINCT (Student_Courses.cID), Courses.coursename, Student_Courses.stu_addedby
FROM Courses RIGHT JOIN Student_Courses ON Courses.cID = Student_Courses.cID;
it shows up this...


cID coursename stu_addedby
1 HLTH 1370 greg
1 HLTH 1370 jane
1 HLTH 1370 john
1 HLTH 1370 san
2 KINT 3370 greg
2 KINT 3370 jane
3 EACH 2110 jane
4 READ 3330 john
5 EACH 4303 greg

Quote Reply
Re: [suezee] Access Query and ASP ..please help! In reply to
Try using DISTINCT on the primary key, like the following:

SELECT DISTINCT (Courses.cID), Courses.coursename, Student_Courses.stu_addedby
FROM Student_Courses RIGHT JOIN Courses ON Courses.cID = Student_Courses.cID;

Also, you could try a sub-query if that doesn't work.
========================================
Buh Bye!

Cheers,
Me