Table Manipulation

I have an excel spreadsheet that has contact data in one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to import it
into an access table but the data is difficult to
manipulate now without manually copying and pasting cells.
Any ideas as to how I can manipulate this data into a more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G

Are the number of rows per address always consistent ie: each address is
exactly 4 rows and followed by a blank and then another address?

If so, you can import these in to a table and allow Access to add an
autonumber primary key. You can then use a crosstab to build your table.
Assuming your table is tblImport with fields ID and Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query. You can then add new
fields to the made table for Town, State, and Zip. Use and update query with
string functions like Mid(), Left(), Right(), Instr(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap" wrote in message
...
I have an excel spreadsheet that has contact data in one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to import it
into an access table but the data is difficult to
manipulate now without manually copying and pasting cells.
Any ideas as to how I can manipulate this data into a more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G

Duane-
Unfortunately, no - some have one (or two) extra address
lines. If I can get the contact information separated
into records with maybe 5 or 6 different "generic" feilds
using the crosstab query, the records could be manually
cleaned up later. The only thing that is consistent in
the spreadsheet is that there is one or two blank rows
between each set of contact information. With only blank
rows as the common trait, could the crosstab you suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent ie:
each address is
exactly 4 rows and followed by a blank and then another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query. You
can then add new
fields to the made table for Town, State, and Zip. Use
and update query with
string functions like Mid(), Left(), Right(), Instr(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"
wrote in message
...
I have an excel spreadsheet that has contact data in one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to import
it
into an access table but the data is difficult to
manipulate now without manually copying and pasting
cells.
Any ideas as to how I can manipulate this data into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.

If the excel file isn't consistant then you may have to use a couple
recordsets in code to step through your imported field records and grab
values to place in a "final" format.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in message
...
Duane-
Unfortunately, no - some have one (or two) extra address
lines. If I can get the contact information separated
into records with maybe 5 or 6 different "generic" feilds
using the crosstab query, the records could be manually
cleaned up later. The only thing that is consistent in
the spreadsheet is that there is one or two blank rows
between each set of contact information. With only blank
rows as the common trait, could the crosstab you suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent ie:
each address is
exactly 4 rows and followed by a blank and then another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query. You
can then add new
fields to the made table for Town, State, and Zip. Use
and update query with
string functions like Mid(), Left(), Right(), Instr(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"
wrote in message
...
I have an excel spreadsheet that has contact data in one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to import
it
into an access table but the data is difficult to
manipulate now without manually copying and pasting
cells.
Any ideas as to how I can manipulate this data into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.

Lets say the records including the blank row in the
spreadsheet are 5, 6 or 7 rows. Are you suggesting that I
need to create a table with the 5, then create another
with using a 6 in the crosstab and then another with a 7
by changing the 5 in the following crosstab? (three
tables?) And then merging them together?I am not sure I
follow.

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;




-----Original Message-----
If the excel file isn't consistant then you may have to
use a couple
recordsets in code to step through your imported field
records and grab
values to place in a "final" format.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Duane-
Unfortunately, no - some have one (or two) extra address
lines. If I can get the contact information separated
into records with maybe 5 or 6 different "generic"
feilds
using the crosstab query, the records could be manually
cleaned up later. The only thing that is consistent in
the spreadsheet is that there is one or two blank rows
between each set of contact information. With only blank
rows as the common trait, could the crosstab you suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent
ie:
each address is
exactly 4 rows and followed by a blank and then another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query. You
can then add new
fields to the made table for Town, State, and Zip. Use
and update query with
string functions like Mid(), Left(), Right(), Instr
(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"
wrote in message
...
I have an excel spreadsheet that has contact data in
one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to
import
it
into an access table but the data is difficult to
manipulate now without manually copying and pasting
cells.
Any ideas as to how I can manipulate this data into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.



.

I would import the records to a table adding an autonumber field. Then open
the records in an ADO or DAO recordset. You can step through the records
accumulating values until you hit a blank record. At that point, create a
new record in a second table with the values. I don't think any type of
crosstab would work if the number of "fields" is not consistent.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in message
...
Lets say the records including the blank row in the
spreadsheet are 5, 6 or 7 rows. Are you suggesting that I
need to create a table with the 5, then create another
with using a 6 in the crosstab and then another with a 7
by changing the 5 in the following crosstab? (three
tables?) And then merging them together?I am not sure I
follow.

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;




-----Original Message-----
If the excel file isn't consistant then you may have to
use a couple
recordsets in code to step through your imported field
records and grab
values to place in a "final" format.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Duane-
Unfortunately, no - some have one (or two) extra address
lines. If I can get the contact information separated
into records with maybe 5 or 6 different "generic"
feilds
using the crosstab query, the records could be manually
cleaned up later. The only thing that is consistent in
the spreadsheet is that there is one or two blank rows
between each set of contact information. With only blank
rows as the common trait, could the crosstab you suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent
ie:
each address is
exactly 4 rows and followed by a blank and then another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query. You
can then add new
fields to the made table for Town, State, and Zip. Use
and update query with
string functions like Mid(), Left(), Right(), Instr
(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"
wrote in message
...
I have an excel spreadsheet that has contact data in
one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to
import
it
into an access table but the data is difficult to
manipulate now without manually copying and pasting
cells.
Any ideas as to how I can manipulate this data into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.



.

I don't understand what you mean by opening the the
records in an ADO or DAO recordset. DO you mean run a
query with all records showing? I also don't understand
what you mean by "You can step through the records
accumulating values until you hit a blank record. At that
point, create a new record in a second table with the
values"
-----Original Message-----
I would import the records to a table adding an
autonumber field. Then open
the records in an ADO or DAO recordset. You can step
through the records
accumulating values until you hit a blank record. At that
point, create a
new record in a second table with the values. I don't
think any type of
crosstab would work if the number of "fields" is not
consistent.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Lets say the records including the blank row in the
spreadsheet are 5, 6 or 7 rows. Are you suggesting
that I
need to create a table with the 5, then create another
with using a 6 in the crosstab and then another with a 7
by changing the 5 in the following crosstab? (three
tables?) And then merging them together?I am not sure I
follow.

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;




-----Original Message-----
If the excel file isn't consistant then you may have to
use a couple
recordsets in code to step through your imported field
records and grab
values to place in a "final" format.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Duane-
Unfortunately, no - some have one (or two) extra
address
lines. If I can get the contact information
separated
into records with maybe 5 or 6 different "generic"
feilds
using the crosstab query, the records could be
manually
cleaned up later. The only thing that is consistent
in
the spreadsheet is that there is one or two blank
rows
between each set of contact information. With only
blank
rows as the common trait, could the crosstab you
suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent
ie:
each address is
exactly 4 rows and followed by a blank and then
another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab
to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query.
You
can then add new
fields to the made table for Town, State, and Zip.
Use
and update query with
string functions like Mid(), Left(), Right(), Instr
(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"

wrote in message
...
I have an excel spreadsheet that has contact data
in
one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to
import
it
into an access table but the data is difficult to
manipulate now without manually copying and
pasting
cells.
Any ideas as to how I can manipulate this data
into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.



.



.

Duane- FYI we adjusted the spreadsheet so that it would
have the same number of rows for each contact. The
crosstab worked great!! Thank you very much for your
solution on this. Jeff G
-----Original Message-----
I would import the records to a table adding an
autonumber field. Then open
the records in an ADO or DAO recordset. You can step
through the records
accumulating values until you hit a blank record. At that
point, create a
new record in a second table with the values. I don't
think any type of
crosstab would work if the number of "fields" is not
consistent.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Lets say the records including the blank row in the
spreadsheet are 5, 6 or 7 rows. Are you suggesting
that I
need to create a table with the 5, then create another
with using a 6 in the crosstab and then another with a 7
by changing the 5 in the following crosstab? (three
tables?) And then merging them together?I am not sure I
follow.

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;




-----Original Message-----
If the excel file isn't consistant then you may have to
use a couple
recordsets in code to step through your imported field
records and grab
values to place in a "final" format.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Duane-
Unfortunately, no - some have one (or two) extra
address
lines. If I can get the contact information
separated
into records with maybe 5 or 6 different "generic"
feilds
using the crosstab query, the records could be
manually
cleaned up later. The only thing that is consistent
in
the spreadsheet is that there is one or two blank
rows
between each set of contact information. With only
blank
rows as the common trait, could the crosstab you
suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent
ie:
each address is
exactly 4 rows and followed by a blank and then
another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab
to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query.
You
can then add new
fields to the made table for Town, State, and Zip.
Use
and update query with
string functions like Mid(), Left(), Right(), Instr
(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"

wrote in message
...
I have an excel spreadsheet that has contact data
in
one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to
import
it
into an access table but the data is difficult to
manipulate now without manually copying and
pasting
cells.
Any ideas as to how I can manipulate this data
into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.



.



.

Glad to hear this worked for you. Excel is a great program but not as a
database.

--
Duane Hookom
MS Access MVP


"Jeff Gilstrap" wrote in message
...
Duane- FYI we adjusted the spreadsheet so that it would
have the same number of rows for each contact. The
crosstab worked great!! Thank you very much for your
solution on this. Jeff G
-----Original Message-----
I would import the records to a table adding an
autonumber field. Then open
the records in an ADO or DAO recordset. You can step
through the records
accumulating values until you hit a blank record. At that
point, create a
new record in a second table with the values. I don't
think any type of
crosstab would work if the number of "fields" is not
consistent.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Lets say the records including the blank row in the
spreadsheet are 5, 6 or 7 rows. Are you suggesting
that I
need to create a table with the 5, then create another
with using a 6 in the crosstab and then another with a 7
by changing the 5 in the following crosstab? (three
tables?) And then merging them together?I am not sure I
follow.

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;




-----Original Message-----
If the excel file isn't consistant then you may have to
use a couple
recordsets in code to step through your imported field
records and grab
values to place in a "final" format.

--
Duane Hookom
MS Access MVP


"Jeff G" wrote in
message
...
Duane-
Unfortunately, no - some have one (or two) extra
address
lines. If I can get the contact information
separated
into records with maybe 5 or 6 different "generic"
feilds
using the crosstab query, the records could be
manually
cleaned up later. The only thing that is consistent
in
the spreadsheet is that there is one or two blank
rows
between each set of contact information. With only
blank
rows as the common trait, could the crosstab you
suggest
be modified to accomodate?
Jeff G
-----Original Message-----
Are the number of rows per address always consistent
ie:
each address is
exactly 4 rows and followed by a blank and then
another
address?

If so, you can import these in to a table and allow
Access to add an
autonumber primary key. You can then use a crosstab
to
build your table.
Assuming your table is tblImport with fields ID and
Field1:

TRANSFORM First(tblImport.Field1) AS FirstOfField1
SELECT [ID]\5 AS Expr1
FROM tblImport
WHERE (((tblImport.Field1) Is Not Null))
GROUP BY [ID]\5
PIVOT ([ID]-1) Mod 5;

Use this query as the basis for a maketable query.
You
can then add new
fields to the made table for Town, State, and Zip.
Use
and update query with
string functions like Mid(), Left(), Right(), Instr
(),...


--
Duane Hookom
MS Access MVP


"Jeff Gilstrap"

wrote in message
...
I have an excel spreadsheet that has contact data
in
one
colume as follows:

name1
company1
street1
town, st zip 1
blank line
name2
company2
street2
town, st zip 2
blank line

each line is in a different row. I would like to
import
it
into an access table but the data is difficult to
manipulate now without manually copying and
pasting
cells.
Any ideas as to how I can manipulate this data
into a
more
user friendly format such as:

name1 company1 street1 town1 st1 zip1
name2 company2 street2 town2 st2 zip2

thanks much
Jeff G



.



.



.