By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,490 Members | 1,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,490 IT Pros & Developers. It's quick & easy.

Does Inner Join work in Access????

P: 46
I have problem joining four tables using Inner Join in ACCESS DB,

I want to join 4 tables: 1, 2, 3 , 4 ....and query data in all these to see, i get a right url associated.

sql1-account id, account name
sql2-account id, email id
sql3-account id, contract number, maintenance start date, maintenance expiration date, download url, contact, maintenance status
sq4-account name, user id, pwd

first- i will check if the USER NAME and PWD I have is the saame as userid and the pwd in sql4
if so- match the account name with the userid and the pwd in sql4 to account id in sql1
then match the corresponding email id for that account id in sql2 with contact in sql3
and if email id=contact and maintenenace status is Y, then get -download url from sql3

for this i have written:
strSQL="SELECT sql4.[User Id], sql4.Pwd, sql1.[ACCOUNT NAME], sql2.[EMAIL ID], sql3.[download URL], "
strSQL= strSQL & " sql3.MAINTENACE_STATUS, sql3.[CONTRACT NUMBER] "
strSQL= strSQL & " FROM ((sql4 INNER JOIN sql1 ON sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME]) "
strSQL= strSQL & " INNER JOIN sql2 ON sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID]) "
strSQL= strSQL & " INNER JOIN sql3 ON sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
strSQL= strSQL & " WHERE (((sql4.[User Id])='" & session(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
strSQL= strSQL & " AND ((sql3.MAINTENACE_STATUS)=Y))"

it gives me internal server error 5005

please help
tanya
Jun 4 '07 #1
Share this Question
Share on Google+
3 Replies


jhardman
Expert 2.5K+
P: 3,405
Tanya,

I'm not sure about inner join, but the following should give the same effect and I use it all the time:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM sql1, sql2, sql3, sql4 WHERE "
  2. strSQL = strSQL & "sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME] AND "
  3. strSQL= strSQL & "sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID])"
  4. strSQL= strSQL & " AND sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
  5. strSQL= strSQL & "AND (((sql4.[User Id])='" & session(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
  6. strSQL= strSQL & " AND ((sql3.MAINTENACE_STATUS)=Y))"
As far as I understand innerjoin, it sets up the same relationship as this. Try it out and tell me if it works.

Jared
Jun 5 '07 #2

P: 46
Tanya,

I'm not sure about inner join, but the following should give the same effect and I use it all the time:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM sql1, sql2, sql3, sql4 WHERE "
  2. strSQL = strSQL & "sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME] AND "
  3. strSQL= strSQL & "sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID])"
  4. strSQL= strSQL & " AND sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
  5. strSQL= strSQL & "AND (((sql4.[User Id])='" & session(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
  6. strSQL= strSQL & " AND ((sql3.MAINTENACE_STATUS)=Y))"
As far as I understand innerjoin, it sets up the same relationship as this. Try it out and tell me if it works.

Jared
====================

Jared, Thanks for replying my questions so patiently...

It didnt work...just gives an error Internal Server Error -500

I dont know for some reason, its not establishing connection to db, is what I feel...


I have a asp page- where customers enter thier user name and the pwd stored in sql server...Once they are logged in- they are taken to another page 2, where they would click on a link to download the software. I have redirected this link to another asap page-3, where I have connected to an access db and running above code....
So- I am not sure, where I am making mistake....Please help..I can send you the page where I am writing all this code. I am pretty sure, that there is no link between asp page-1 where the customer logs in and clicks on this link...
The asp page code where I have written the above code is:

------------------------------





<%@ language=VBScript%>

<% Option Explicit


'Declare Local Variables

Dim conn, strSQL
Dim strEmail,strUrl
Dim objMail, objRS
Dim strMessageBody, strDisclaimer
Dim sql1, sql2, sql3, sql4, UserName, Password
Dim CONTRACT NUMBER, MAINTENANCE_START_DATE_C, MAINTENANCE_EXPIRATION_DATE_C,downloadURL
Dim ESD_Contact, MAINTENANCE_STATUS, User_ID, Pwd
'End Variable Declaration

%>

<html><head><title>Posting Mail</title></head>
<body bgcolor=acqua leftmargin='1' topmargin='1'><table width='100%' height='100%' cellspacing='0' cellpadding='2'>
<tr align='left'><td height='100%' width='145' valign='top'></td><td width='555' valign='top' align='left'>

'<%'get form elements and compose email

'Function ReplaceSpecial(str)
'If NOT IsNull(str) Then
'ReplaceSpecial = Replace(str, "'", "''")
'End If
'End Function

'strEmail = Trim(ReplaceSpecial(Request.Form("User_Email")))

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "DSN=SQL"
Set objRS = Server.CreateObject("ADODB.Recordset")

session("UserName")="abd"
session("Password")="123"

strSQL = "SELECT * FROM sql1, sql2, sql3, sql4 WHERE "
strSQL = strSQL & "sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME] AND "
strSQL= strSQL & "sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID])"
strSQL= strSQL & " AND sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
strSQL= strSQL & "AND (((sql4.[User Id])='" & session(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
strSQL= strSQL & " AND ((sql3.MAINTENACE_STATUS)=Y))"


'strSQL="SELECT sql4.[User Id] as UId, sql4.Pwd as pwd, sql1.[ACCOUNT NAME] as AccName, "
'strSQL= strSQL & " sql1.[ACCOUNT ID] as AccID , sql2.[EMAIL ID] as email, sql3.[download URL] as surl "
'strSQL= strSQL & " sql3.MAINTENACE_STATUS, sql3.[CONTRACT NUMBER] "
'strSQL= strSQL & " FROM ((sql4 INNER JOIN sql1 ON sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME]) "
'strSQL= strSQL & " INNER JOIN sql2 ON sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID] ) "
'strSQL= strSQL & " INNER JOIN sql3 ON sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
'strSQL= strSQL & " WHERE sql4.[User Id]='abd' AND sql4.[Pwd]='123'"
'strSQL= strSQL & " WHERE sql4.[User Id]='" & session("UserName") & "'"
'strSQL= strSQL & " AND sql4.[Pwd]='" & session("Password") & "'"
'strSQL= strSQL & " AND sql3.MAINTENACE_STATUS=Yes"

'strSQL ="SELECT * FROM sql1, sql2, sql3, sql4 where sql3.User Id=Session (UserName) And Pwd=Session(Password)"
'strSQL =strSQL & "And sql4.ACCOUNT NAME=sql1.ACCOUNT NAME And sql1.ACCOUNT ID=sql3.ACCOUNT ID"
'strSQL =strSQL & " And sql2.EMAIL ID=sql3.ESD Contact WHERE (sql3.MAINTENANCE CONTRACT)='Y'"


objRS.open strSQL,conn

'response.write strEmail

'response.write strSQL


If NOT objRS.EOF Then
'CREATE THE MESSAGE OBJECT
response.write objRS("UId")
response.write objRS("Pwd")
response.write objRS("AccName")
response.write objRS("AccID")
response.write objRS("email")
response.write objRS("surl")
strEmail=objRS("email")
'objRS("sql3.ESD Contact")

strUrl="http://" & objRS("surl")
'objRS("sql3.download URL")


Set objMail = Server.CreateObject("CDO.Message")
'This section provides the configuration information for the remote SMTP server.

objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Send the message using the network (SMTP over the network).
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.myserver.com"
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 'Use SSL for the connection (True or False)
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
'End remote SMTP server configuration section


objMail.Subject = "Download your software electronically"
objMail.From = "myID@myserver.com"
objMail.To = strEmail

'objMail.MailFormat = 0 'MIME Format
'objMail.BodyFormat = 0
strMessageBody = "Hi, You can download from this site <a href='" & strUrl & "' > " & strUrl & "</a>"


strMessageBody = strMessageBody & "<br><br>Thank You,"
strDisclaimer = "<br><br><p><font color='#777777' size='-1.5' face='Arial'><i>This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.</i></font>"

'objMail.Body = strMessageBody & strDisclaimer
objMail.HTMLBody= strMessageBody & strDisclaimer

response.write strMessageBody
'objMail.Send

objRS.Close

Set objRS = Nothing

%>

<table>
<tr><td class="normal">Your request has been processed. Email address is: <%=strEmail%> Software URL is: <%=strUrl%>

</td></tr>
<tr><td class="normal"></td></tr>
<tr><td class="normal">Thank You</td></tr>
</table>
<%Else%>
<table>
<tr><td class="normal"><font size='+1' color='Red'><b>Your request could not be processed. You will need to check the information that was submitted </b></font></td></tr>
</table>
<%End If%>

</td>
</tr>
</table>
<hr>

</body>
</html>




------------------------------------------
Jun 5 '07 #3

P: 46
====================

Jared, Thanks for replying my questions so patiently...

It didnt work...just gives an error Internal Server Error -500

I dont know for some reason, its not establishing connection to db, is what I feel...


I have a asp page- where customers enter thier user name and the pwd stored in sql server...Once they are logged in- they are taken to another page 2, where they would click on a link to download the software. I have redirected this link to another asap page-3, where I have connected to an access db and running above code....
So- I am not sure, where I am making mistake....Please help..I can send you the page where I am writing all this code. I am pretty sure, that there is no link between asp page-1 where the customer logs in and clicks on this link...
The asp page code where I have written the above code is:

------------------------------





<%@ language=VBScript%>

<% Option Explicit


'Declare Local Variables

Dim conn, strSQL
Dim strEmail,strUrl
Dim objMail, objRS
Dim strMessageBody, strDisclaimer
Dim sql1, sql2, sql3, sql4, UserName, Password
Dim CONTRACT NUMBER, MAINTENANCE_START_DATE_C, MAINTENANCE_EXPIRATION_DATE_C,downloadURL
Dim ESD_Contact, MAINTENANCE_STATUS, User_ID, Pwd
'End Variable Declaration

%>

<html><head><title>Posting Mail</title></head>
<body bgcolor=acqua leftmargin='1' topmargin='1'><table width='100%' height='100%' cellspacing='0' cellpadding='2'>
<tr align='left'><td height='100%' width='145' valign='top'></td><td width='555' valign='top' align='left'>

'<%'get form elements and compose email

'Function ReplaceSpecial(str)
'If NOT IsNull(str) Then
'ReplaceSpecial = Replace(str, "'", "''")
'End If
'End Function

'strEmail = Trim(ReplaceSpecial(Request.Form("User_Email")))

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "DSN=SQL"
Set objRS = Server.CreateObject("ADODB.Recordset")

session("UserName")="abd"
session("Password")="123"

strSQL = "SELECT * FROM sql1, sql2, sql3, sql4 WHERE "
strSQL = strSQL & "sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME] AND "
strSQL= strSQL & "sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID])"
strSQL= strSQL & " AND sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
strSQL= strSQL & "AND (((sql4.[User Id])='" & session(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
strSQL= strSQL & " AND ((sql3.MAINTENACE_STATUS)=Y))"


'strSQL="SELECT sql4.[User Id] as UId, sql4.Pwd as pwd, sql1.[ACCOUNT NAME] as AccName, "
'strSQL= strSQL & " sql1.[ACCOUNT ID] as AccID , sql2.[EMAIL ID] as email, sql3.[download URL] as surl "
'strSQL= strSQL & " sql3.MAINTENACE_STATUS, sql3.[CONTRACT NUMBER] "
'strSQL= strSQL & " FROM ((sql4 INNER JOIN sql1 ON sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME]) "
'strSQL= strSQL & " INNER JOIN sql2 ON sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID] ) "
'strSQL= strSQL & " INNER JOIN sql3 ON sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
'strSQL= strSQL & " WHERE sql4.[User Id]='abd' AND sql4.[Pwd]='123'"
'strSQL= strSQL & " WHERE sql4.[User Id]='" & session("UserName") & "'"
'strSQL= strSQL & " AND sql4.[Pwd]='" & session("Password") & "'"
'strSQL= strSQL & " AND sql3.MAINTENACE_STATUS=Yes"

'strSQL ="SELECT * FROM sql1, sql2, sql3, sql4 where sql3.User Id=Session (UserName) And Pwd=Session(Password)"
'strSQL =strSQL & "And sql4.ACCOUNT NAME=sql1.ACCOUNT NAME And sql1.ACCOUNT ID=sql3.ACCOUNT ID"
'strSQL =strSQL & " And sql2.EMAIL ID=sql3.ESD Contact WHERE (sql3.MAINTENANCE CONTRACT)='Y'"


objRS.open strSQL,conn

'response.write strEmail

'response.write strSQL


If NOT objRS.EOF Then
'CREATE THE MESSAGE OBJECT
response.write objRS("UId")
response.write objRS("Pwd")
response.write objRS("AccName")
response.write objRS("AccID")
response.write objRS("email")
response.write objRS("surl")
strEmail=objRS("email")
'objRS("sql3.ESD Contact")

strUrl="http://" & objRS("surl")
'objRS("sql3.download URL")


Set objMail = Server.CreateObject("CDO.Message")
'This section provides the configuration information for the remote SMTP server.

objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Send the message using the network (SMTP over the network).
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.myserver.com"
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 'Use SSL for the connection (True or False)
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
'End remote SMTP server configuration section


objMail.Subject = "Download your software electronically"
objMail.From = "myID@myserver.com"
objMail.To = strEmail

'objMail.MailFormat = 0 'MIME Format
'objMail.BodyFormat = 0
strMessageBody = "Hi, You can download from this site <a href='" & strUrl & "' > " & strUrl & "</a>"


strMessageBody = strMessageBody & "<br><br>Thank You,"
strDisclaimer = "<br><br><p><font color='#777777' size='-1.5' face='Arial'><i>This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.</i></font>"

'objMail.Body = strMessageBody & strDisclaimer
objMail.HTMLBody= strMessageBody & strDisclaimer

response.write strMessageBody
'objMail.Send

objRS.Close

Set objRS = Nothing

%>

<table>
<tr><td class="normal">Your request has been processed. Email address is: <%=strEmail%> Software URL is: <%=strUrl%>

</td></tr>
<tr><td class="normal"></td></tr>
<tr><td class="normal">Thank You</td></tr>
</table>
<%Else%>
<table>
<tr><td class="normal"><font size='+1' color='Red'><b>Your request could not be processed. You will need to check the information that was submitted </b></font></td></tr>
</table>
<%End If%>

</td>
</tr>
</table>
<hr>

</body>
</html>




------------------------------------------
Jared- It worked for me, the code that you have sent. ....Thanks a lot- there were few changes that had to be made though.....
Thanks so much
Tanya
Jun 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.