By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Help separating some data

100+
P: 365
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)

Expand|Select|Wrap|Line Numbers
  1. Line Description
  2. LW4255 - PASTETTE LARGE BULB MICRO L155MM 8.0ML STERILE 20S - PACK OF 200
  3. LW4061 - PASTETTE FINE TIP L147MM 3.3ML STERILE 1S - PACK OF 400
  4. 4200074578 FINE TIP PASTETTE
  5. 4200074578 FINE TIP PASTETTE
  6. 442265 - BD BACTEC LYTIC/10 ANAEROBIC/F MEDIUM VIAL - BOX OF 50
  7. 442192 - BD BACTEC PLUS AEROBIC/F MEDIUM VIAL - BOX OF 50
  8. 4200074591 BACTEC PLUS
  9. 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
Jun 3 '09 #1
Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,679
@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.
Jun 3 '09 #2

100+
P: 365
@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:
Expand|Select|Wrap|Line Numbers
  1. 'Function to elute 10 digit numbers Val(Left([Line Description],10))
  2.     DoCmd.RunSQL "SELECT tblAll.*, Val(Left([Line Description],10)) AS Filt INTO tblMayVat FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
  3.     DoCmd.RunSQL "DELETE tblAll.*, Val(Left([Line Description],10)) AS Filt FROM tblAll WHERE (((Val(Left([Line Description],10)))>4000000000));"
  4.     DoCmd.RunSQL "SELECT tblAll.* INTO tblMay FROM tblAll;"
  5.     DoCmd.RunSQL "DELETE tblAll.* FROM tblAll;"
  6.  
this will do for now unless there is an easier way?

Thanks for replying ADezii

Dan
Jun 3 '09 #3

ADezii
Expert 5K+
P: 8,679
@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.
Jun 3 '09 #4

ADezii
Expert 5K+
P: 8,679
@Dan2kx
Here is another approach that you can take, Dan2kx. I also included an Attachment for you to view.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstOriginal As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5. Set rstOriginal = MyDB.OpenRecordset("tblOriginal", dbOpenForwardOnly)
  6.  
  7. CurrentDb.Execute "Delete * from tbl10Digits;"
  8. CurrentDb.Execute "Delete * from tblNon10Digits;"
  9.  
  10. With rstOriginal
  11.   Do While Not .EOF
  12.     If IsNumeric(Left$(![CodeField], 10)) Then
  13.       CurrentDb.Execute "Insert Into tbl10Digits ([CodeField]) Values ('" & _
  14.                          ![CodeField] & "');", dbFailOnError
  15.     Else
  16.       CurrentDb.Execute "Insert Into tblNon10Digits ([CodeField]) Values ('" & _
  17.                          ![CodeField] & "');", dbFailOnError
  18.     End If
  19.     .MoveNext
  20.   Loop
  21. End With
  22.  
  23. rstOriginal.Close
  24. Set rstOriginal = Nothing
Jun 4 '09 #5

NeoPa
Expert Mod 15k+
P: 31,707
I would suggest two queries of the form :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [NewTable1] ([Line],[Description])
  2. SELECT *
  3. FROM [YourTable]
  4. WHERE Len([Line])=10
and :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [NewTable2] ([Line],[Description])
  2. SELECT *
  3. FROM [YourTable]
  4. WHERE Len([Line])<>10
Jun 4 '09 #6

100+
P: 365
Sorry NeoPa the data is in the same field (unfortunately)
Jun 6 '09 #7

NeoPa
Expert Mod 15k+
P: 31,707
@Dan2kx
I don't follow you Dan (Data in same field [Line] - was already quite clear).

Have you tried this suggestion?
Jun 6 '09 #8

100+
P: 365
the data i submitted was an extract from just one field

[Line Description]
Jun 6 '09 #9

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jun 6 '09 #10

NeoPa
Expert Mod 15k+
P: 31,707
The replacement SQL uses the InStr() function. This ensures that only records where the first space is found after 10 characters are selected :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [NewTable1] ([Line Description], ...)
  2. SELECT *
  3. FROM [YourTable]
  4. WHERE InStr(1,[Line Description],' ')=11
and :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [NewTable2] ([Line Description], ...)
  2. SELECT *
  3. FROM [YourTable]
  4. WHERE InStr(1,[Line Description],' ')<>11
Jun 6 '09 #11

100+
P: 365
That would work, hope your head is OK
Jun 6 '09 #12

100+
P: 675
If a 10 character, non-numeric is possible in positions 1-10, this might have to be expanded to
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [NewTable1] ([Line Description], ...) 
  2. SELECT * 
  3. FROM [YourTable] 
  4. WHERE InStr(1,[Line Description],' ')=11 AND IsNumeric(Left([Line Description], 10))
and:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [NewTable2] ([Line Description], ...) 
  2. SELECT * 
  3. FROM [YourTable] 
  4. WHERE InStr(1,[Line Description],' ')<>11 OR Not IsNumeric(Left([Line Description], 10))
Jun 6 '09 #13

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. Public Declare Function timeGetTime Lib "winmm.dll" () As Long
Expand|Select|Wrap|Line Numbers
  1. Dim lngStart As Long
  2. Dim lngEnd As Long
  3. Dim strSQL As String
  4. Dim strSQL2 As String
  5.  
  6. lngStart = timeGetTime()
  7.  
  8. CurrentDb.Execute "Delete * from tbl10Digits;"
  9. CurrentDb.Execute "Delete * from tblNon10Digits;"
  10.  
  11. DoCmd.Hourglass True
  12.  
  13. strSQL = "INSERT INTO [tbl10Digits] ([CodeField]) " & _
  14.          "SELECT * FROM [tblOriginal] WHERE InStr(1,[CodeField],' ')=11;"
  15. CurrentDb.Execute strSQL
  16.  
  17. strSQL2 = "INSERT INTO [tblNon10Digits] ([CodeField]) " & _
  18.          "SELECT * FROM [tblOriginal] WHERE InStr(1,[CodeField],' ')<>11;"
  19. CurrentDb.Execute strSQL2
  20.  
  21. DoCmd.Hourglass False
  22.  
  23. lngEnd = timeGetTime()
  24.  
  25. 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.
Jun 6 '09 #14

100+
P: 365
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)
Jun 6 '09 #15

NeoPa
Expert Mod 15k+
P: 31,707
@Dan2kx
I rather assumed that may be possible, hence the InStr() instead of the simpler Mid() function usage.
Jun 6 '09 #16

NeoPa
Expert Mod 15k+
P: 31,707
@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.
Jun 6 '09 #17

100+
P: 365
@NeoPa
not sure i follow? that would organise the data incorrectly?
Jun 6 '09 #18

NeoPa
Expert Mod 15k+
P: 31,707
@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 :
Expand|Select|Wrap|Line Numbers
  1. 442092 - B D BACTEC PLUS AEROBIC/F MEDIUM VIAL - BOX OF 50
The code using InStr() works correctly of course.
Jun 6 '09 #19

100+
P: 365
Sorry my turn for the head smacking

Thanks for the help
Jun 7 '09 #20

NeoPa
Expert Mod 15k+
P: 31,707
Be gentle with yourself. A damaged head will only cause you more confusion :D
Jun 7 '09 #21

Post your reply

Sign in to post your reply or Sign up for a free account.