Spilt comma delimited field in a MS Access table

[wytco0]

I have a table that conatins 2 fields, the first is an application
name and the second contains a list of servers separated by commas.

I need to change this so that I have a list of application-server
pairs.

I am only a basis access user and I have not been able to work out how
to do this.

I am getting the original data from an excel spreadsheet that someone
else is providing and I cant get them to change the format !

example:

This is how it looks now
App Servers
------------------ -----------------------------------
Application 1 Server1, Server2, Server3

And I need
App Servers
------------------ -----------------------------------
Application 1 Server1
Application 1 Server2
Application 1 Server3


Any ideas, preferably simple ones !


Thanks

 

[Jeff Boyce]

Before you import the data, use Excel and parse the string into multiple
fields.

In Access, you can use a series of queries to append sets of data from your
input values (either a linked or imported table) to your final table design.
The first query would "gather" Application and FirstColumnOfServerName, the
second would pull in Application and SecondColumnOfServerName, the third...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[wytco0]

Before you import the data, use Excel and parse the string into multiple
fields.

In Access, you can use a series of queries to append sets of data from your
input values (either a linked or imported table) to your final table design.
The first query would "gather" Application and FirstColumnOfServerName, the
second would pull in Application and SecondColumnOfServerName, the third...

Hi Jeff, thanks for that, I think I can handle the Excel bit you
mention but I am confused aboiut the second bit in Access, how to I
gather the application/server pairs of the table? what sort of code do
i need? is this something built into access? I did say I dont know
Access very well!

Any clues much appreciated.

 

[Jeff Boyce]

Please re-read my response.

Use queries. Create a query that returns the Application and the ServerName
in the first ServerName field.

Change the query to an append query and append to your (more) permanent
table.

"Rinse & repeat"...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/