Help separating some data

**ADezii** · Jun 3 '09, 11:09 AM

Originally posted by Dan2kx

Good eve peeps,

Just a quicky (hopefully)

Just wondering how i can seperate some data, unfortunately it is not in a very logical format,( this is just one field of the data)

Code:

Line Description
LW4255 - PASTETTE LARGE BULB MICRO L155MM 8.0ML STERILE 20S - PACK OF 200
LW4061 - PASTETTE FINE TIP L147MM 3.3ML STERILE 1S - PACK OF 400
4200074578 FINE TIP PASTETTE
4200074578 FINE TIP PASTETTE
442265 - BD BACTEC LYTIC/10 ANAEROBIC/F MEDIUM VIAL - BOX OF 50
442192 - BD BACTEC PLUS AEROBIC/F MEDIUM VIAL - BOX OF 50
4200074591 BACTEC PLUS
4200074591 BACTEC PLUS

i want the lines that start with a 10 digit number seperating from those that are otherwise, (probably into two tables), not sure which function to use really (in SQL)

TIA

Dan

To the best of my knowledge, there is no known functionality within SQL that will do what you request, but wait and see what other Members come up with since they are better versed in SQL than I. What you are requesting can be done, however, with a combination of DAO and VBA code. First and foremost, a couple of questions:

What is the Name of the Table containing this data?
You stated that this data exists in a single Field, what is the Name of this Field?
Will the 10 digit entry, if it exists, always be at the start of the Field?
Is there a possibility that multiple, 10-digit numbers, can exist in the same Record?
Will there always be a Space after a 10-digit entry if it exists?
What is the Name of the Table/Field into which you want the Records 'with' 10-digit entries Appended?
What is the Name of the Table/Field into which you want the Records 'without' 10-digit entries Appended?
If you are interested in a code-based solution, kindly answer the questions as accurately as possible, if not, simply ignore this Post.

**Dan2kx** · Jun 3 '09, 12:03 PM

Originally posted by ADezii

What is the Name of the Table containing this data?
data comes from excel not bothered what the table is named
You stated that this data exists in a single Field, what is the Name of this Field?
as above or "[Line Description]"
Will the 10 digit entry, if it exists, always be at the start of the Field?
i am led to believe so
Is there a possibility that multiple, 10-digit numbers, can exist in the same Record?
possibly, to be honest im not sure
Will there always be a Space after a 10-digit entry if it exists?
hopefully
What is the Name of the Table/Field into which you want the Records 'with' 10-digit entries Appended?
again doesnt matter
What is the Name of the Table/Field into which you want the Records 'without' 10-digit entries Appended?
as above
If you are interested in a code-based solution, kindly answer the questions as accurately as possible, if not, simply ignore this Post.

this data is not mine as you might have guessed, this seperation is only the first step in the manipulation process, subsequently i will need to use DAO etc

i did manage to create some SQL that seperates the data and supprisingly to work based on the following:

Code:

'Function to elute 10 digit numbers Val(Left([Line Description],10))
    DoCmd.RunSQL "SELECT tblAll.*, Val(Left([Line Description],10)) AS Filt INTO tblMayVat FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
    DoCmd.RunSQL "DELETE tblAll.*, Val(Left([Line Description],10)) AS Filt FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
    DoCmd.RunSQL "SELECT tblAll.* INTO tblMay FROM tblAll;"
    DoCmd.RunSQL "DELETE tblAll.* FROM tblAll;"

this will do for now unless there is an easier way?

Thanks for replying ADezii

Dan

**ADezii** · Jun 3 '09, 05:15 PM

Originally posted by Dan2kx

this data is not mine as you might have guessed, this seperation is only the first step in the manipulation process, subsequently i will need to use DAO etc

i did manage to create some SQL that seperates the data and supprisingly to work based on the following:

Code:

'Function to elute 10 digit numbers Val(Left([Line Description],10))
    DoCmd.RunSQL "SELECT tblAll.*, Val(Left([Line Description],10)) AS Filt INTO tblMayVat FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
    DoCmd.RunSQL "DELETE tblAll.*, Val(Left([Line Description],10)) AS Filt FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
    DoCmd.RunSQL "SELECT tblAll.* INTO tblMay FROM tblAll;"
    DoCmd.RunSQL "DELETE tblAll.* FROM tblAll;"

this will do for now unless there is an easier way?

Thanks for replying ADezii

Dan

I'l post what I come up with when I get a chance, posting the results into 2 Tables, thus giving you another approach.

**ADezii** · Jun 4 '09, 12:19 AM

Originally posted by Dan2kx

this data is not mine as you might have guessed, this seperation is only the first step in the manipulation process, subsequently i will need to use DAO etc

i did manage to create some SQL that seperates the data and supprisingly to work based on the following:

Code:

'Function to elute 10 digit numbers Val(Left([Line Description],10))
    DoCmd.RunSQL "SELECT tblAll.*, Val(Left([Line Description],10)) AS Filt INTO tblMayVat FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
    DoCmd.RunSQL "DELETE tblAll.*, Val(Left([Line Description],10)) AS Filt FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
    DoCmd.RunSQL "SELECT tblAll.* INTO tblMay FROM tblAll;"
    DoCmd.RunSQL "DELETE tblAll.* FROM tblAll;"

this will do for now unless there is an easier way?

Thanks for replying ADezii

Dan

Here is another approach that you can take, Dan2kx. I also included an Attachment for you to view.

Code:

Dim MyDB As DAO.Database
Dim rstOriginal As DAO.Recordset

Set MyDB = CurrentDb()
Set rstOriginal = MyDB.OpenRecordset("tblOriginal", dbOpenForwardOnly)

CurrentDb.Execute "Delete * from tbl10Digits;"
CurrentDb.Execute "Delete * from tblNon10Digits;"

With rstOriginal
  Do While Not .EOF
    If IsNumeric(Left$(![CodeField], 10)) Then
      CurrentDb.Execute "Insert Into tbl10Digits ([CodeField]) Values ('" & _
                         ![CodeField] & "');", dbFailOnError
    Else
      CurrentDb.Execute "Insert Into tblNon10Digits ([CodeField]) Values ('" & _
                         ![CodeField] & "');", dbFailOnError
    End If
    .MoveNext
  Loop
End With

rstOriginal.Close
Set rstOriginal = Nothing

**NeoPa** · Jun 4 '09, 12:05 PM

I would suggest two queries of the form :

Code:

INSERT INTO [NewTable1] ([Line],[Description])
SELECT *
FROM [YourTable]
WHERE Len([Line])=10

and :

Code:

INSERT INTO [NewTable2] ([Line],[Description])
SELECT *
FROM [YourTable]
WHERE Len([Line])<>10

**Dan2kx** · Jun 6 '09, 11:25 AM

Sorry NeoPa the data is in the same field (unfortunately)

**NeoPa** · Jun 6 '09, 12:02 PM

Originally posted by Dan2kx

Sorry NeoPa the data is in the same field (unfortunately)

I don't follow you Dan (Data in same field [Line] - was already quite clear).

Have you tried this suggestion?

**Dan2kx** · Jun 6 '09, 12:05 PM

the data i submitted was an extract from just one field

[Line Description]

**NeoPa** · Jun 6 '09, 02:23 PM

AAaaaaah (smacks head in frustration with self)!

I missed that (quite important) detail. Even though it was there at the start.

Give me 5 & I'll post a SQL solution for this scenario.

**NeoPa** · Jun 6 '09, 03:35 PM

The replacement SQL uses the InStr() function. This ensures that only records where the first space is found after 10 characters are selected :

Code:

INSERT INTO [NewTable1] ([Line Description], ...)
SELECT *
FROM [YourTable]
WHERE InStr(1,[Line Description],' ')=11

and :

Code:

INSERT INTO [NewTable2] ([Line Description], ...)
SELECT *
FROM [YourTable]
WHERE InStr(1,[Line Description],' ')<>11

**Dan2kx** · Jun 6 '09, 04:42 PM

That would work, hope your head is OK

**OldBirdman** · Jun 6 '09, 05:21 PM

If a 10 character, non-numeric is possible in positions 1-10, this might have to be expanded to

Code:

INSERT INTO [NewTable1] ([Line Description], ...) 
SELECT * 
FROM [YourTable] 
WHERE InStr(1,[Line Description],' ')=11 AND IsNumeric(Left([Line Description], 10))

and:

Code:

INSERT INTO [NewTable2] ([Line Description], ...) 
SELECT * 
FROM [YourTable] 
WHERE InStr(1,[Line Description],' ')<>11 OR Not IsNumeric(Left([Line Description], 10))

**ADezii** · Jun 6 '09, 05:59 PM

Just out of curiosity, I ran some simple Benchmark Tests against the 2-Phase SQL code that NeoPa posted in Post #11. NeoPa's code processed 127,000 Records and populated the appropriate Tables with the relevant Values, either 10-Digit prefix or not, in an Average of 3.474 seconds over Multiple Trials. I thought the results were pretty impressive. Nice job NeoPa. I posted the Base Test Code below if anyone is interested:

Code:

Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Code:

Dim lngStart As Long
Dim lngEnd As Long
Dim strSQL As String
Dim strSQL2 As String

lngStart = timeGetTime()

CurrentDb.Execute "Delete * from tbl10Digits;"
CurrentDb.Execute "Delete * from tblNon10Digits;"

DoCmd.Hourglass True

strSQL = "INSERT INTO [tbl10Digits] ([CodeField]) " & _
         "SELECT * FROM [tblOriginal] WHERE InStr(1,[CodeField],' ')=11;"
CurrentDb.Execute strSQL

strSQL2 = "INSERT INTO [tblNon10Digits] ([CodeField]) " & _
         "SELECT * FROM [tblOriginal] WHERE InStr(1,[CodeField],' ')<>11;"
CurrentDb.Execute strSQL2

DoCmd.Hourglass False

lngEnd = timeGetTime()

Debug.Print "The 2-Phase SQL approach took: " & (lngEnd - lngStart) / 1000 & " seconds to execute"

P.S. - I also thought that OldBirdman made a valid point in Post #13, so I modified the Test Code to include his additional Criteria. Average Execution Time for the SQL including OldBirdman's approach was 3.736 seconds.

**Dan2kx** · Jun 6 '09, 06:05 PM

it could oocur by chance that there is a space in the 11th character of the short code data.

which was my reasoning for the Val(left([Line Description],10)) that AFAIK would filter only the numeric 10 digit numbers? i just had a problem selecting the others (so i moved then deleted the 10's)

Help separating some data

Help separating some data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment