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

inner join query in access not working- can anyone look into this pls

P: 46
Hi,,,

I have 2 tables in access: tblUrl (software Url, contract number- 2 columns)
and tblAccContract(4 columns-accountid, contract id, contract date, email addr)

I am querying these two tables using inner join. I get the result properly, but unable to retrieve the values from those tables. I think the problem is with the query I used for joining these two tables.

Please check with this:

The output has to be like:"Download your software here: www.google.com...."
But it says: "Download your software here..."
It doesnt display the Url in the tblUrl table


code is:


strSQL ="SELECT tblAccContract.accountId,tblAccContract.emailAddr, tblAccContract.contractNo, tblUrl.softwareUrl"
strSQL =strSQL & " FROM tblAccContract INNER JOIN tblUrl ON tblAccContract.contractNo = tblUrl.contractNo"
strSQL =strSQL & " WHERE (tblAccContract.emailAddr)='" & strEmail &"'"
objRS.open strSQL,conn
strUrl=objRS("tblUrl.softwareUrl")
response.write strUrl


Anyone familiar with this - please check with the code....I just need to get the values from multiple tables when Join is sued in Access.


Thanks
Tanya
May 22 '07 #1
Share this Question
Share on Google+
1 Reply


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

Welcome to the scripts DN.

I've never used "inner Join" before. you can relate the tables like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblAccContract, tblUrl WHERE tblAccContract.contractNo = tblUrl.contractNo AND emailAddr='" & strEmail &"'"
That said, I don't know why your code didn't work. I would suggest trying this and putting in a simple loop to list everything in the record set to troublshoot:
Expand|Select|Wrap|Line Numbers
  1. for each x in objRS.fields
  2.    response.write x & ": " & objRS(x) & "<br>" & vbNewLine
  3. next
  4.  
Let me know if this helps.

Jared
May 22 '07 #2

Post your reply

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