Join on field that contains multiple items

[Guest]

I have reports that are emailed each month and usually about 50 are returned.
I need to notify the Managers of the accounts. The manager name and emails
are stored in a [Main] table. The email address field can contain multiple
email addresses separated by semi-colons.

I want to put the returned email addresses in a table and somehow join it to
the [Main] table using the email address.......I just don't know how to join
a field in one table to a field in another where you want to match to an item
in a list stored in a single field.

Any ideas?


****
(Please save the lectures on Normalization--I'm trying to work with what I'm
given here. I don't have the authority to change the tables. This is a
process that I do manually each month and wanted to try to autamate it if
possible)
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Guest]

I figured out how to do this

in the [tblReturnedEmail].[ReturnedEmail] field I added a * before and after
the email address. So the wildcard character is actually stored in the field
value.

ex *[email protected]*


SELECT [Main].[Agent ID], [Main].[Bank Name], [Main].RAE, [Main].[Reports
Email], [tblReturnedEmail].[ReturnedEmail]
FROM [*BankMain], [tblReturnedEmail]
WHERE ((([Main].[Reports Email]) Like [tblReturnedEmail].[ReturnedEmail]));
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Guest]

Billy,

Rather than add the asterix to the field in your Errors table, just modify
the query:

SELECT [Main].[Agent ID]
, [Main].[Bank Name]
, [Main].RAE, [Main].[Reports Email]
, [tblReturnedEmail].[ReturnedEmail]
FROM [*BankMain], [tblReturnedEmail]
WHERE [Main].[Reports Email]) Like "*" & [tblReturnedEmail].[ReturnedEmail]
& "*"

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I figured out how to do this

in the [tblReturnedEmail].[ReturnedEmail] field I added a * before and after
the email address. So the wildcard character is actually stored in the field
value.

ex *[email protected]*


SELECT [Main].[Agent ID], [Main].[Bank Name], [Main].RAE, [Main].[Reports
Email], [tblReturnedEmail].[ReturnedEmail]
FROM [*BankMain], [tblReturnedEmail]
WHERE ((([Main].[Reports Email]) Like [tblReturnedEmail].[ReturnedEmail]));
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

I have reports that are emailed each month and usually about 50 are returned.
I need to notify the Managers of the accounts. The manager name and emails
are stored in a [Main] table. The email address field can contain multiple
email addresses separated by semi-colons.

I want to put the returned email addresses in a table and somehow join it to
the [Main] table using the email address.......I just don't know how to join
a field in one table to a field in another where you want to match to an item
in a list stored in a single field.

Any ideas?


****
(Please save the lectures on Normalization--I'm trying to work with what I'm
given here. I don't have the authority to change the tables. This is a
process that I do manually each month and wanted to try to autamate it if
possible)
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Guest]

Thanks, that's a definite improvement....takes out a step in the process.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Billy,

Rather than add the asterix to the field in your Errors table, just modify
the query:

SELECT [Main].[Agent ID]
, [Main].[Bank Name]
, [Main].RAE, [Main].[Reports Email]
, [tblReturnedEmail].[ReturnedEmail]
FROM [*BankMain], [tblReturnedEmail]
WHERE [Main].[Reports Email]) Like "*" & [tblReturnedEmail].[ReturnedEmail]
& "*"

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I figured out how to do this

in the [tblReturnedEmail].[ReturnedEmail] field I added a * before and after
the email address. So the wildcard character is actually stored in the field
value.

ex *[email protected]*


SELECT [Main].[Agent ID], [Main].[Bank Name], [Main].RAE, [Main].[Reports
Email], [tblReturnedEmail].[ReturnedEmail]
FROM [*BankMain], [tblReturnedEmail]
WHERE ((([Main].[Reports Email]) Like [tblReturnedEmail].[ReturnedEmail]));
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

I have reports that are emailed each month and usually about 50 are returned.
I need to notify the Managers of the accounts. The manager name and emails
are stored in a [Main] table. The email address field can contain multiple
email addresses separated by semi-colons.

I want to put the returned email addresses in a table and somehow join it to
the [Main] table using the email address.......I just don't know how to join
a field in one table to a field in another where you want to match to an item
in a list stored in a single field.

Any ideas?


****
(Please save the lectures on Normalization--I'm trying to work with what I'm
given here. I don't have the authority to change the tables. This is a
process that I do manually each month and wanted to try to autamate it if
possible)
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003