Inner join recordset

[beckyr51]

Can someone help me with my syntax here. I am trying to send an email to people if they have at least one student in the faculty specified by an inputbox. This works but it sends as many emails as there are students in that faculty i.e: if someone has 2 students in a faculty they get 2 emails when i only want them to get 1. Here's my code:

Set rs = db.OpenRecordset("Select [tblTutor].[TU_EMAIL]from [tblTutor]inner join [tblStudents] on [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE where [tblStudents].[STU_FAC_NO] = " & "'" & fac & "';")

 

[HarleyQuinn]

Try this:

Set rs = db.OpenRecordset("Select [red]DISTINCT[/red] [tblTutor].[TU_EMAIL]from [tblTutor]inner join [tblStudents] on [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE where [tblStudents].[STU_FAC_NO] =  " & "'" & fac & "';")

Using DISTINCT will only allow unique rows of data to be returned (like a GROUP BY).

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[beckyr51]

Thanks