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

Find value in field

P: 2
Hi,

I'm having troubles with the following:

I have 2 tables, Candidates and Attachments. In the Candidate there is a column called email1.

In the Attachment table, there is a column called Text filled with text. Somewhere in this text there is a email address mentioned (which is email1).

I want to find the records where candidate.email1 is mentioned in the attachment.text column.

I therefore tried the following query:

Expand|Select|Wrap|Line Numbers
  1. SELECT candidate_id FROM candidate, attachment WHERE email1 IN(attachment.text)
This is returing the correct value only if there isn't any other text in the attachment.text column, which is not the fact.

Anyone that can help me with this?
Mar 17 '08 #1
Share this Question
Share on Google+
3 Replies


Scott Price
Expert 100+
P: 1,384
You have your email1 and attachment.text swapped. The sql you have is looking IN email1 FOR attachment.text.

Regards,
Scott
Mar 17 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Also, if you actually have a column/field named Text you need to change the name to something else; Text is a Reserved Word in Access, and sooner or later it'll cause you trouble.

Welcome to TheScripts!

Linq ;0)>
Mar 17 '08 #3

P: 2
Also, if you actually have a column/field named Text you need to change the name to something else; Text is a Reserved Word in Access, and sooner or later it'll cause you trouble.

Welcome to TheScripts!

Linq ;0)>
Thank you both, I've managed to accomplish it by extracting the email addresses out of the text in excel. However, when I want to run the query
Expand|Select|Wrap|Line Numbers
  1.  UPDATE attachment SET attachment.data_item_id= (SELECT Distinct candidate_id FROM candidate, attachment
  2. WHERE candidate.email1= attachment.email);
I'm getting the error "Operation must use an updateable query." I don't see what I'm doing wrong here. Both the attachment.data_item_id and Candidate.candidate_id are numeric and the tables are having a Primary Key.

Running the Select query apart from the Update query does return the expected value.
Mar 19 '08 #4

Post your reply

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