Many- to -many relationship table help

[msluke]

Right now the page I'm working one displays all the information in the "Info" table in a table format. The comment field displays an image if a comment has been entered and no image if there is no comment. When the user clicks on the image it links to the original form (another page) and displays the whole form with the comment.

Now what I need to do is remove or clear the image from the table once the comment has been viewed by a user.
A comment can be viewed by many users. And a given user can view many comments. So I created a junction table (called ViewedComments) between Info and Users tables.

ViewedComments
userID (autonumber)
UID (text)


Users
userID (autonumber)
password (text)
.
.
.

Info
ID (autonumber)
UID (text)
.
.
.
.
comments (text)


I have a few questons. First can I use the UID number (from the Info table) to 'check' for a comment. Because a UID can exist even if there is no comment entered but it's the only thing that is unique in this table. And the second is with my query - right now it doesn't do anything. And last I know I have to ad an entry to the ViewedComments table after a given message is viewed by a given user...but I'm not exactly sure how.

This is my code now:

strConnect2 = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\inetpub\

http://wwwroot\dir\dir\our.mdb"

Dim asql, rsusers
asql = "select * from Users"
set rsusers = server.createobject("adodb.recordset&quot
rsusers.open asql, strConnect2, 3

currentUser = rsuser("userID&quot
//rs is the recordset to the "Info" table
currentMsg = rs("UID&quot

'Creating the recordset to the ViewedComments table

strConnect3 = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\inetpub\

http://wwwroot\dir\dir\our.mdb"

Dim rsvc, sql3
sql3 = "SELECT * FROM ViewedComments WHERE userID = '& currentUser &' AND UID = '& currentMsg &' "
set rsvc = server.createobject("adodb.recordset&quot
rsvc.open sql3, strConnect3, 3

.
.
.
.

<td align=center><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;1&quot;>
<%

if rs(&quot;comments&quot <> &quot;&quot; Then
If rsvc.EOF Then
response.write &quot;<a href='edit.asp?num=&quot; & rs(&quot;id&quot & &quot; '><img src='images/letter.gif' width='20' height='20' border=0></a>&quot;

//I know I should
End IF
Else
response.write &quot;&nbsp&quot;
End If

.
.
.
.
</td>
</tr>

<%
if row_color = &quot;#e1f5ff&quot; then
row_color = &quot;#ffffff&quot;
else
row_color = &quot;#e1f5ff&quot;
end if
rs.movenext
Loop

rslogin.close
set rslogin = nothing
rsvc.close
set rsvc = nothing



I get no errors but it doesn't do anything. It just displays the info as before.
Any suggestions, ideas, thoughts would be appreciated.
Thanks.

 

[Darrylles]

Hi,

Too long, complicated - narrow it down - then submit it, that's why no answers.

Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[msluke]

Ok fair enough.

Why don't I start by asking some questions strickly about many-to-many tables (relationships).

As mentioned above I have 3 tables Info, Users and ViewedComments. ViewedComments is the junction or many-to-many table. In it I have two fields:
userID(autonumber)
UID (text)

Do the foreign keys in my junction table HAVE to be PRIMARY keys from the other two tables (Info and Users)?

Do I need to assign a primary key in the junction table?

I hope that narrows it down a bit for now.
Thanks.