Splitting 1 column into 2 columns

[Guest]

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains one
column comprising of Group headers and the members of those groups, like this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

....etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Jeff Boyce]

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Each group begins with the [<group name>] header, although each group
contains an unfixed number of names within, and varies significantly.
In Excel, this can be done easily with some playing around and the following
formula:
=IF(LEFT(A2,4)="[SEC",RIGHT(A2,LEN(A2)-FIND("=",A2,1)),B1) where B1 is a
simple "=A1" formula (the first group header starts at A1).
(This is a "live" example, where all my group headers start with phrase "SEC")

In Excel this is very easy, but I am pretty new to Access and don't know how
to replicate this!
Thanks.

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains
one
column comprising of Group headers and the members of those groups, like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Denis Dougall]

I would suggest reading about left/right/mid they are probably what you seem
to be looking for. This example has one column "Name" and the result is
FirstName and LastName which can be done because the Name field has them
separated / delimited by a comma (in your example it looks like an "=" was
used)

FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)
LastName: Left$([Name],InStr(1,[Name],",")-1)

That's the type of code to put in the select statement

Select Name from Customer.table
where FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)
LastName: Left$([Name],InStr(1,[Name],",")-1)
order by LastName;

I hope that helps!

Denis

Each group begins with the [<group name>] header, although each group
contains an unfixed number of names within, and varies significantly.
In Excel, this can be done easily with some playing around and the following
formula:
=IF(LEFT(A2,4)="[SEC",RIGHT(A2,LEN(A2)-FIND("=",A2,1)),B1) where B1 is a
simple "=A1" formula (the first group header starts at A1).
(This is a "live" example, where all my group headers start with phrase "SEC")

In Excel this is very easy, but I am pretty new to Access and don't know how
to replicate this!
Thanks.

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains
one
column comprising of Group headers and the members of those groups, like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Guest]

Thanks Denis, This kind-of along the right lines, except that I am not
looking to split a single field (i.e. "Joe Bloggs" into "Joe" and "Bloggs")
more to take each Group heading and pull out the member of the group.

I would suggest reading about left/right/mid they are probably what you seem
to be looking for. This example has one column "Name" and the result is
FirstName and LastName which can be done because the Name field has them
separated / delimited by a comma (in your example it looks like an "=" was
used)

FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)
LastName: Left$([Name],InStr(1,[Name],",")-1)

That's the type of code to put in the select statement

Select Name from Customer.table
where FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)
LastName: Left$([Name],InStr(1,[Name],",")-1)
order by LastName;

I hope that helps!

Denis

Each group begins with the [<group name>] header, although each group
contains an unfixed number of names within, and varies significantly.
In Excel, this can be done easily with some playing around and the following
formula:
=IF(LEFT(A2,4)="[SEC",RIGHT(A2,LEN(A2)-FIND("=",A2,1)),B1) where B1 is a
simple "=A1" formula (the first group header starts at A1).
(This is a "live" example, where all my group headers start with phrase "SEC")

In Excel this is very easy, but I am pretty new to Access and don't know how
to replicate this!
Thanks.

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains
one
column comprising of Group headers and the members of those groups, like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Jeff Boyce]

I can't tell, from your example, if each group has a "header record" that is
the literal string "[<group name>]" or if you are using this as a variable,
and your actual group names are something like:

My Group
or
[My Group]
or
<My Group>

The If statement you included implies that the group names are actually more
like:

[Secxxxxxxx]

One way to approach parsing one column into multiple columns, depending on
what's in the field, would be to use Denis' suggestion of looking into the
Left(), Right() and Mid() functions. Also look into the Instr() function
(to find a space or a comma).

In a query, you can use the IIF() function to "fill" new fields (i.e.
columns). By using similar IIF() statements in both a "GroupName" column
and a "PersonName" column, you could get the first part of the parsing done.

That IIF() statements might look something like:

IIF(Left([YourColumn],4) = "[SEC", [YourColumn],null)

and

IIF(Left([YourColumn],4) <> "[SEC", [YourColumn],null)

for the new "GroupName" and "PersonName" columns respectively.

Regards

Jeff Boyce
<Office/Access MVP>

Each group begins with the [<group name>] header, although each group
contains an unfixed number of names within, and varies significantly.
In Excel, this can be done easily with some playing around and the
following
formula:
=IF(LEFT(A2,4)="[SEC",RIGHT(A2,LEN(A2)-FIND("=",A2,1)),B1) where B1 is a
simple "=A1" formula (the first group header starts at A1).
(This is a "live" example, where all my group headers start with phrase
"SEC")

In Excel this is very easy, but I am pretty new to Access and don't know
how
to replicate this!
Thanks.

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains
one
column comprising of Group headers and the members of those groups,
like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Guest]

Sorry, it is pretty difficult to explain!

Yes, the literal display of the file, including square brackets, is:

Column1
[SECxxxxxxx1]
Name1
Name2
Name3

[SECxxxxxxx2]
Name4
Name5
Name6
Name7

The desired end result would be (again literally- this is how it looks in
excel):

Column1 Column2
[SECxxxxxxxx1] [SECxxxxxxxx1]
Name1 [SECxxxxxxxx1]
Name2 [SECxxxxxxxx1]
Name3 [SECxxxxxxxx1]
[SECxxxxxxxx2] [SECxxxxxxxx2]
Name4 [SECxxxxxxxx2]
Name5 [SECxxxxxxxx2]
Name6 [SECxxxxxxxx2]
Name7 [SECxxxxxxxx2]

The problem I have is that, in Excel at least, I could say "if column1
begins with SEC, then take this field, otherwise take the field directly
above" so that column 2 would only change when the [SECxxxxxxx] header
changes in column 1. Then I could range fill this to the bottom of the file.
But as Access doesn't have "cells" as such, I don't know how to reference the
value directly above.

Hope that makes sense!!

I can't tell, from your example, if each group has a "header record" that is
the literal string "[<group name>]" or if you are using this as a variable,
and your actual group names are something like:

My Group
or
[My Group]
or
<My Group>

The If statement you included implies that the group names are actually more
like:

[Secxxxxxxx]

One way to approach parsing one column into multiple columns, depending on
what's in the field, would be to use Denis' suggestion of looking into the
Left(), Right() and Mid() functions. Also look into the Instr() function
(to find a space or a comma).

In a query, you can use the IIF() function to "fill" new fields (i.e.
columns). By using similar IIF() statements in both a "GroupName" column
and a "PersonName" column, you could get the first part of the parsing done.

That IIF() statements might look something like:

IIF(Left([YourColumn],4) = "[SEC", [YourColumn],null)

and

IIF(Left([YourColumn],4) <> "[SEC", [YourColumn],null)

for the new "GroupName" and "PersonName" columns respectively.

Regards

Jeff Boyce
<Office/Access MVP>

Each group begins with the [<group name>] header, although each group
contains an unfixed number of names within, and varies significantly.
In Excel, this can be done easily with some playing around and the
following
formula:
=IF(LEFT(A2,4)="[SEC",RIGHT(A2,LEN(A2)-FIND("=",A2,1)),B1) where B1 is a
simple "=A1" formula (the first group header starts at A1).
(This is a "live" example, where all my group headers start with phrase
"SEC")

In Excel this is very easy, but I am pretty new to Access and don't know
how
to replicate this!
Thanks.

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains
one
column comprising of Group headers and the members of those groups,
like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Jeff Boyce]

Please review my previous post. Using a query and the IIF() statements,
you'd get something like:

Col1 Col2 Col3
[SECx1] [SECx1]
Name1 Name1
Name2 Name2

Aha! This won't help you, as you still don't have [SECx1] connected to
Name2 in the same record.

I suspect you'll need to write a function that steps through the input data
one row at a time. If the field value is like "[SEC", your code will start
a new record.

Each row after that will get the same value of "[SEC", plus the Name value.

When the code encounters the next "[SEC" value, it starts over on creating
the next new record.

Good luck!

Jeff Boyce
<Office/Access MVP>

Sorry, it is pretty difficult to explain!

Yes, the literal display of the file, including square brackets, is:

Column1
[SECxxxxxxx1]
Name1
Name2
Name3

[SECxxxxxxx2]
Name4
Name5
Name6
Name7

The desired end result would be (again literally- this is how it looks in
excel):

Column1 Column2
[SECxxxxxxxx1] [SECxxxxxxxx1]
Name1 [SECxxxxxxxx1]
Name2 [SECxxxxxxxx1]
Name3 [SECxxxxxxxx1]
[SECxxxxxxxx2] [SECxxxxxxxx2]
Name4 [SECxxxxxxxx2]
Name5 [SECxxxxxxxx2]
Name6 [SECxxxxxxxx2]
Name7 [SECxxxxxxxx2]

The problem I have is that, in Excel at least, I could say "if column1
begins with SEC, then take this field, otherwise take the field directly
above" so that column 2 would only change when the [SECxxxxxxx] header
changes in column 1. Then I could range fill this to the bottom of the
file.
But as Access doesn't have "cells" as such, I don't know how to reference
the
value directly above.

Hope that makes sense!!

I can't tell, from your example, if each group has a "header record" that
is
the literal string "[<group name>]" or if you are using this as a
variable,
and your actual group names are something like:

My Group
or
[My Group]
or
<My Group>

The If statement you included implies that the group names are actually
more
like:

[Secxxxxxxx]

One way to approach parsing one column into multiple columns, depending
on
what's in the field, would be to use Denis' suggestion of looking into
the
Left(), Right() and Mid() functions. Also look into the Instr() function
(to find a space or a comma).

In a query, you can use the IIF() function to "fill" new fields (i.e.
columns). By using similar IIF() statements in both a "GroupName" column
and a "PersonName" column, you could get the first part of the parsing
done.

That IIF() statements might look something like:

IIF(Left([YourColumn],4) = "[SEC", [YourColumn],null)

and

IIF(Left([YourColumn],4) <> "[SEC", [YourColumn],null)

for the new "GroupName" and "PersonName" columns respectively.

Regards

Jeff Boyce
<Office/Access MVP>

Each group begins with the [<group name>] header, although each group
contains an unfixed number of names within, and varies significantly.
In Excel, this can be done easily with some playing around and the
following
formula:
=IF(LEFT(A2,4)="[SEC",RIGHT(A2,LEN(A2)-FIND("=",A2,1)),B1) where B1 is
a
simple "=A1" formula (the first group header starts at A1).
(This is a "live" example, where all my group headers start with phrase
"SEC")

In Excel this is very easy, but I am pretty new to Access and don't
know
how
to replicate this!
Thanks.

:

How do you (or Access) know when one "group" ends and another begins?

Jeff Boyce
<Office/Access MVP>

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which
contains
one
column comprising of Group headers and the members of those groups,
like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if
at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

 

[Michael J. Strickland]

Hi folks,

I am having a real dilemma, and am hoping you can help.

I have exported a text file into an Access (2003) table, which contains
one
column comprising of Group headers and the members of those groups, like
this:

[Group1]
Person1
Person2
Person3

[Group2]
Person4
Person5
Person6

...etc. (there are about 100 groups)

I need to change the format to two separate columns, in order for my
reports
and queries to work, into this format:

Person1 Group1
Person2 Group1
Person3 Group1
Person4 Group2
Person5 Group2
Person6 Group2

Please can anyone tell me how this is done? Preferably SQL or VBA if at
all
possible!

Thanks in advance for your help - this is driving me mad!
Jason

This might be easier to do in the text file.

You can open the file in MS Word and use the advanced search and replace to
reformat it as a tab delimited text file with one line per group.
For the example you show:

1. Open the text file in MS Word.
3. Replace all group separators ( ^p^p[ ) with a marker ( e.g. $$ ).
4. Replace all line breaks ( ^p ) with tabs ( ^t ).
5. Replace all markers ( $$ ) with line breaks ( ^p ).

Now you have a tab delimited file with one line for each group.
At the top of the file, add a line with field names separated by tabs (as
many as your max # of Persons in a group).

5. Save the file as a text file.

You can import this text file to Access using the import wizard.


--