Professional Web Applications Themes

Seperating data within a field - ASP Database

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 ...

  1. #1

    Default Seperating data within a field

    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
    Darren Guest

  2. #2

    Default Re: Seperating data within a field

    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" <darrenstahlhuthotmail.com> schreef in bericht
    news:898d00a0.0404010241.5e1652bbposting.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 Guest

  3. #3

    Default Re: Seperating data within a field


    You can also seek for DIV

    p=Instr(text," div")

    part1 = left(text,p-1)

    etc...


    Maarten Guest

  4. #4

    Default Re: Seperating data within a field

    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"


    Bob Barrows Guest

  5. #5

    Default Re: Seperating data within a field

    Dear Bob, is this SQL or just Access Sql


    Maarten Guest

  6. #6

    Default Re: Seperating data within a field

    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.


    Bob Barrows [MVP] Guest

  7. #7

    Default Re: Seperating data within a field

    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)
    Evertjan. Guest

  8. #8

    Default Re: Seperating data within a field

    "Maarten" <nobodypandora.be> wrote in message news:<PmTac.57393$8B.3750963phobos.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
    Darren Guest

  9. #9

    Default Re: Seperating data within a field


    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*apandora.be (remove all *)








    "Darren" <darrenstahlhuthotmail.com> schreef in bericht
    news:898d00a0.0404011700.a4e1a46posting.google.co m...
    > "Maarten" <nobodypandora.be> wrote in message
    news:<PmTac.57393$8B.3750963phobos.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 Guest

  10. #10

    Default Re: Seperating data within a field

    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"


    Bob Barrows Guest

  11. #11

    Default Re: Seperating data within a field

    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message news:<#XoTXD#FEHA.3080tk2msftngp13.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
    Darren Guest

Similar Threads

  1. Comparing current field data with last entry for field
    By ccnorris in forum Coldfusion - Advanced Techniques
    Replies: 8
    Last Post: June 1st, 03:20 AM
  2. Spotcolour in a DCS is seperating addional on other Spotplates
    By Achim_von_Flatow@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 26
    Last Post: May 1st, 12:31 AM
  3. seperating filenames
    By John Smith in forum ASP
    Replies: 1
    Last Post: September 16th, 01:55 PM
  4. Replies: 3
    Last Post: August 15th, 10:52 AM
  5. seperating ascx and cs files
    By Salim Afar in forum ASP.NET General
    Replies: 0
    Last Post: July 10th, 01:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139