troubleshooting Question

MS Access. Splitting a column with various data into as many data was in the column lines

Avatar of HelpdeskJBC
HelpdeskJBCFlag for Austria asked on
Microsoft AccessMicrosoft SQL ServerSQL
9 Comments3 Solutions527 ViewsLast Modified:
Hi there!

Problem is we need to have a better overview of this table 'tbl_MailAdr'.

We have there various email addresses in one column seperated by ';' sometimes there are unneeded spaces inbetween as well " ".

Basic Info:
Here the two tables. (I created the second one. From that one we can put with queries the data there together.)

tbl_MailAdr
Group:		Number, Primary key
Name:		Text, Primary key
Company:	Number, Primary Key
MailAdr:	Memo
enabled:	Yes/No

tbl_MailAdr_single
Count:		AutoNumber, Primary key
Group:		Number
Name:		Text
Company:	Number
MailAdr:	Memo
enabled:	Yes/No
Description what is needed:
This 'tbl_MailAdr' lies on a sql server. It is prefered that we get a solution for MS Access 2003.

I need a process where we copy all data lines from tbl_MailAdr to tbl_MailAdr_single.

In the field

tbl_MailAdr.MailAdr

there could be either no data in it, one email without any ';' in the end or more then 20 email addresses with ';' in it and sometimes with ' ' in it.

What needs to be automatically processed is copy the data from tbl_MailAdr to tbl_MailAdr_single and create for each line the same data however, with a the seperated email addresses AND a different count (auto)number.

If the process contains a delete 'tbl_MailAdr_single' before processing would be ideal but is not needed since that is fast done by hand or a delete query.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros