Seperating data within a field

[Darren]

Hi All,

I am designing a website for a tennis club. Each week Tennis Australia
release an excel spreadsheet of last weekend's results. I import this
into my access DB for use on the website.

"The problem"
The first coloumn of the spreadsheet is the clubname and division. I
need to seperate the two.

At the moment when I import the data I get this

tablename:
tblResults

fieldname:
ClubAndDivision

records:
Mellor Park Div1
Henley Beach Div1
West Lakes Div2
Mellor Park Div2
Henley Beach Div4

What I would like is this instead

tablename:
tblResults

fieldnames:
Club Division

records:
Mellor Park Div1 (or just the number)
Henley Beach Div1
West Lakes Div2
Mellor Park Div2
Henley Beach Div4

I've tried to find a solution using a query but I'm not that good at
access yet. I've even asked Tennis Australia to change their
spreadsheet but they said no. Any ideas?

Thanks in advance,
Darren Stahlhut

[Maarten]

Why not importing the whole text in the first field

After importing you select the record with a blank second field

DO WHILE NOT RS.eof

vTxt = Trim(RS("firstField"))
For x = len(vTxt) to 1 step-1
If Mid(vTxt, x, 1) = " " then
part1 = trim(left(vTxt,x-1))
part2 = trim(right(vTxt, len(vTxt)-x))
end if

set instruction RS("firstField") = part1
set instruction RS("secondField") = part2

RS.MoveNext

Loop







"Darren" <darrenstahlhut@hotmail.com> schreef in bericht
news:898d00a0.0404010241.5e1652bb@posting.google.c om...

> Hi All,
>
> I am designing a website for a tennis club. Each week Tennis Australia
> release an excel spreadsheet of last weekend's results. I import this
> into my access DB for use on the website.
>
> "The problem"
> The first coloumn of the spreadsheet is the clubname and division. I
> need to seperate the two.
>
> At the moment when I import the data I get this
>
> tablename:
> tblResults
>
> fieldname:
> ClubAndDivision
>
> records:
> Mellor Park Div1
> Henley Beach Div1
> West Lakes Div2
> Mellor Park Div2
> Henley Beach Div4
>
> What I would like is this instead
>
> tablename:
> tblResults
>
> fieldnames:
> Club Division
>
> records:
> Mellor Park Div1 (or just the number)
> Henley Beach Div1
> West Lakes Div2
> Mellor Park Div2
> Henley Beach Div4
>
> I've tried to find a solution using a query but I'm not that good at
> access yet. I've even asked Tennis Australia to change their
> spreadsheet but they said no. Any ideas?
>
> Thanks in advance,
> Darren Stahlhut

[Maarten]

You can also seek for DIV

p=Instr(text," div")

part1 = left(text,p-1)

etc...

[Bob Barrows]

Darren wrote:

> Hi All,
>
> I am designing a website for a tennis club. Each week Tennis Australia
> release an excel spreadsheet of last weekend's results. I import this
> into my access DB for use on the website.
>
> "The problem"
> The first coloumn of the spreadsheet is the clubname and division. I
> need to seperate the two.
>

It is definitely a good idea to separate them, especially if you have a need
to select or sort records by division.

So the first step is to initially import the data into a work table, instead
of directly into tblResults. Then use this query to insert the records into
the tblResults table (which I am assuming will already exist with the proper
field structure):

Insert Into tblResults (club,division)
Select
Left(ClubAndDivision,InStr(ClubAndDivision," Div")-1),
Mid(ClubAndDivision,Instr(ClubAndDivision," Div")+4)
From worktable

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Maarten]

Dear Bob, is this SQL or just Access Sql

[Bob Barrows [MVP]]

Maarten wrote:

> Dear Bob, is this SQL or just Access Sql

Instr() and Mid() are VBA functions, so this query will only work in Access.
Use the corresponding T-SQL functions for SQL Server:
CHARINDEX and SUBSTRING.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

Maarten wrote on 01 apr 2004 in microsoft.public.inetserver.asp.db:

>> records:
>> Mellor Park Div1
>> Henley Beach Div1
>> West Lakes Div2
>> Mellor Park Div2
>> Henley Beach Div4

> Why not importing the whole text in the first field
>
> After importing you select the record with a blank second field
>
> DO WHILE NOT RS.eof
>
> vTxt = Trim(RS("firstField"))
> For x = len(vTxt) to 1 step-1
> If Mid(vTxt, x, 1) = " " then
> part1 = trim(left(vTxt,x-1))
> part2 = trim(right(vTxt, len(vTxt)-x))
> end if
>
> set instruction RS("firstField") = part1
> set instruction RS("secondField") = part2
>
> RS.MoveNext
>
> Loop

DO WHILE NOT RS.eof

vTxt = Trim(RS("firstField"))
aTxt = split(vTxt, " ")
part1 = aTxt(0)
for i=1 to ubound(aTxt)-1
part1 = part1 & " " & aTxt(i)
next
part2 = aTxt(ubound(aTxt))

set instruction RS("firstField") = part1
set instruction RS("secondField") = part2

RS.MoveNext

Loop




--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Darren]

"Maarten" <nobody@pandora.be> wrote in message news:<PmTac.57393$8B.3750963@phobos.telenet-ops.be>...

> You can also seek for DIV
>
> p=Instr(text," div")
>
> part1 = left(text,p-1)
>
> etc...

Hi Maarten,

Is there a way to do it inside the database? I would like to be able
to sort the recordset by club or division and so on and run some
queries.

Your method worked though, and seperating the field on the text "Div"
would work out best for me because there is no set character length
for the field, the club name size and division size could vary. Ie.
Div1 or Div12.

Thanks again,
Darren Stahlhut

[Maarten]

What do you mean with "inside the database"

See the article of Bob, with his info you can do it in the ACCESS database

Insert Into tblResults (club,division)
Select
Left(ClubAndDivision,InStr(ClubAndDivision," Div")-1),
Mid(ClubAndDivision,Instr(ClubAndDivision," Div")+4)
From worktable
Bob Barrows

Split or seek for " Div" space+div

If there is a club Water Divers Div1 you come in problems.

You can reach me on m*e*d*i*a@pandora.be (remove all *)








"Darren" <darrenstahlhut@hotmail.com> schreef in bericht
news:898d00a0.0404011700.a4e1a46@posting.google.co m...

> "Maarten" <nobody@pandora.be> wrote in message

news:<PmTac.57393$8B.3750963@phobos.telenet-ops.be>...

> > You can also seek for DIV
> >
> > p=Instr(text," div")
> >
> > part1 = left(text,p-1)
> >
> > etc...

>
> Hi Maarten,
>
> Is there a way to do it inside the database? I would like to be able
> to sort the recordset by club or division and so on and run some
> queries.
>
> Your method worked though, and seperating the field on the text "Div"
> would work out best for me because there is no set character length
> for the field, the club name size and division size could vary. Ie.
> Div1 or Div12.
>
> Thanks again,
> Darren Stahlhut

[Bob Barrows]

Maarten wrote:

> What do you mean with "inside the database"
>
> See the article of Bob, with his info you can do it in the ACCESS
> database
>
> Insert Into tblResults (club,division)
> Select
> Left(ClubAndDivision,InStr(ClubAndDivision," Div")-1),
> Mid(ClubAndDivision,Instr(ClubAndDivision," Div")+4)
> From worktable
> Bob Barrows
>
> Split or seek for " Div" space+div
>
> If there is a club Water Divers Div1 you come in problems.
>

This is true, but it is easily fixed by using InStrRev instead of InStr:

Insert Into tblResults (club,division)
SELECT
Left(ClubAndDivision,InStrRev(ClubAndDivision,"Div ")-1),
Mid(ClubAndDivision,InstrRev(ClubAndDivision,"Div" )+3)
FROM worktable;

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Darren]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<#XoTXD#FEHA.3080@tk2msftngp13.phx.gbl>...

> Darren wrote:

> > Hi All,
> >
> > I am designing a website for a tennis club. Each week Tennis Australia
> > release an excel spreadsheet of last weekend's results. I import this
> > into my access DB for use on the website.
> >
> > "The problem"
> > The first coloumn of the spreadsheet is the clubname and division. I
> > need to seperate the two.
> >

>
> It is definitely a good idea to separate them, especially if you have a need
> to select or sort records by division.
>
> So the first step is to initially import the data into a work table, instead
> of directly into tblResults. Then use this query to insert the records into
> the tblResults table (which I am assuming will already exist with the proper
> field structure):
>
> Insert Into tblResults (club,division)
> Select
> Left(ClubAndDivision,InStr(ClubAndDivision," Div")-1),
> Mid(ClubAndDivision,Instr(ClubAndDivision," Div")+4)
> From worktable
>
> HTH,
> Bob Barrows

Hi Bob, that's perfect :)

Thanks everyone,
Darren Stahlhut