Help separating some data

[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)

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

[ADezii]

@Dan2kx
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:

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

[Dan2kx]

@ADezii
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:

'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]

@Dan2kx
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]

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

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]

I would suggest two queries of the form :

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

and :

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

[Dan2kx]

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

[NeoPa]

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

Have you tried this suggestion?

[Dan2kx]

the data i submitted was an extract from just one field

[Line Description]

[NeoPa]

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]

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

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

and :

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

[Dan2kx]

That would work, hope your head is OK

[OldBirdman]

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

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

and:

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

[ADezii]

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:

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

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]

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)

[NeoPa]

@Dan2kx
I rather assumed that may be possible, hence the InStr() instead of the simpler Mid() function usage.

[NeoPa]

@ADezii
Thanks for the compliment ADezii.

I found very early on in my work with Access (when I tried to do it another way) that using SQL to execute any changes was orders of magnitude faster than trying to process the data manually in the VBA code.

[Dan2kx]

@NeoPa
not sure i follow? that would organise the data incorrectly?

[NeoPa]

@Dan2kx
I assume you are unsure why the Mid() function call would not work correctly.

It would fail in that the following data would be treated as a ten character start string, when in reality it should not be :

442092 - B D BACTEC PLUS AEROBIC/F MEDIUM VIAL - BOX OF 50

The code using InStr() works correctly of course.

[Dan2kx]

Sorry my turn for the head smacking

Thanks for the help

[NeoPa]

Be gentle with yourself. A damaged head will only cause you more confusion :D