Hi Ken,
I will do that but as I will be setting up a database with the data
that I
import I thought that access would be the best program for it.
So if you have no objections please will you show me excatly how to use
the
Mid
function in access to achieve my goal
Many thanks,
William
:
Actually, you can parse the numbers into separate digits in EXCEL. If
this
is the only reason you're using ACCESS, then that is way overkill.
You can write macros (VBA) in EXCEL to parse out data and write the
data
into other cells. There also is a worksheet function MID that will do
similar things to what I suggested in the ACCESS query.
May I suggest that you post your question in one of the EXCEL
newsgroups?
People there can give you recommendations on how to do all of this in
EXCEL.
You don't gain anything in this situation to use ACCESS as an
intermediate
process.
--
Ken Snell
<MS ACCESS MVP>
Ok I think that you need the whole situation, I have a program in
excel
that
I need to import data into, I import the original raw data into
notepad
and
each peice of the data is separated by a comma, no problem to import
this
data into an excel sheet, BUT one piece of the data has a string
of
4
or 5
numbers and i need to separate those numbers so that each single
diget
take
up one column, this cannot be done in excel so that data has to be
imported
into access and manipulated and than re-exported it to the excel
program
Here is an example of the raw data in notepad
H,1,King Josh,100,55.5,10,P.Ferris,72620,tcd
Here is the names of the columns that the data needs to go into
H,TabNo,RName,IRat,Wght,BP,Jock,1strun,2ndrun,3rdrun,4thrun,5thrun,key
You will note that the raw data contains a string "72620", this
string
needs
to be separated into columns 1strun,2ndrun, etc.
At this stage I have not entered a relationship between the original
table
"
race 1" and the temp table "table 2" which could be part of the
problem
but
not to sure because i have not used access for the last 5 years and
am
a
bit
rusty
PS using access 2000
Many thanks,
William
:
You need to tell us some important information:
-- how are you importing the data into the temporary table (by
what
process)?
-- what is the name of the temporary table into which you've
imported
the raw data?
-- what are the names of all the fields in the temporary table,
and
which field holds the number string?
-- are you wanting to append data to the "linked table" from
the
temporary table?
--
Ken Snell
<MS ACCESS MVP>
No not working, please treat me like a dummy and take me through
it
step
by
step
to make it easy lets say we import only one column that column
has a
string
of 5 numbers (54321) and we need to put each of those numbers
into a
separate
column so each column holds only a single digit. The table which
it
needs
to
link to has 5 columns named Number 1, Number 2, Number 3, etc
a table query has the following rows
Field:
Table:
Sort:
Show:
Criteria:
or:
What do I enter into each row to achieve my goal
Many thanks
William
:
Replace Field1, Field2, Field3, Field4, etc. with the real names
of
those
fields in the temp table.
If those are the real names. then get rid of the ColA and ColB
and
such
that
you apparently have in the "Field:" box.
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
I think that I did this, I imported the data into a temp
table(table1)
and
than went to design view (query) and using table1 selected in
the
"field"
ColA Field 1, ColB Field 2,ColC Field 3, in the 4th field area
ColD
I
entered
Expr1: [ Mid(Field4,1,1)] and in the next Field area ColE I
entered
Expr1:
[
Mid(Field4,2,1)] etc
When i run the query I get a box asking me to enter a
parameter
value
and
whatever I enter is what comes up in that column. What have I
done
wrong
because doing this I may as well go to the original text and
manually
insert
commas between each number
Regards,
William
:
I would import the data into a temporary table in ACCESS,
letting
the
number
digits go into a single field. Then use the Mid function in a
query
to
separate the numbers:
SELECT Field1, Field2, Field3, Mid(Field4,1,1) AS ColD,
Mid(Field4,2,1) AS ColE, Mid(Field4,3,1) AS ColF,
Mid(Field4,4,1) AS ColG, Mid(Field4,5,1) AS ColH,
Field5 FROM TableName;
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
The data comes in as a text file and each word is separated
by
a
comma
except for the numbers which come as one string
an example
peter, jones,plumber, 12345, master
I need to put this data into 9 columns
a.. column A peter
b.. column B jones
c.. column C plumber
d.. column D 1
e.. column E 2
f.. column F 3
g.. column G 4
h.. column H 5
i.. column I master
Many thanks,
William
:
Most likely what you seek to do can be done, but you don't
give
us
enough
information to provide suggestions. How are you wanting to
"split"
the
numbers? Show us sample data as you import it, and sample
of
how
it
should
be exported.
--
Ken Snell
<MS ACCESS MVP>
message
Please help,
I import information into an access data base, one of
the
columns
has
between 3 and 5 numbers, I need to export this into an
excel
work
sheet
so
that each number goes into a separate column,
unfortunately
the
numbers
do
not have any type of separater eg 12345, is there any
way
that
I
can
import
these number either into access or from access to excel
to
form a
separte
column for each number. Please note the external data
has
about
400
