473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help separating some data

365 100+
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
20 1784
ADezii
8,834 Expert 8TB
@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
Dan2kx
365 100+
@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
8,834 Expert 8TB
@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
8,834 Expert 8TB
@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
32,556 Expert Mod 16PB
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
Dan2kx
365 100+
Sorry NeoPa the data is in the same field (unfortunately)
Jun 6 '09 #7
NeoPa
32,556 Expert Mod 16PB
@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
Dan2kx
365 100+
the data i submitted was an extract from just one field

[Line Description]
Jun 6 '09 #9
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
Dan2kx
365 100+
That would work, hope your head is OK
Jun 6 '09 #12
OldBirdman
675 512MB
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
8,834 Expert 8TB
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
Dan2kx
365 100+
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
32,556 Expert Mod 16PB
@Dan2kx
I rather assumed that may be possible, hence the InStr() instead of the simpler Mid() function usage.
Jun 6 '09 #16
NeoPa
32,556 Expert Mod 16PB
@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
Dan2kx
365 100+
@NeoPa
not sure i follow? that would organise the data incorrectly?
Jun 6 '09 #18
NeoPa
32,556 Expert Mod 16PB
@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
Dan2kx
365 100+
Sorry my turn for the head smacking

Thanks for the help
Jun 7 '09 #20
NeoPa
32,556 Expert Mod 16PB
Be gentle with yourself. A damaged head will only cause you more confusion :D
Jun 7 '09 #21

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

Similar topics

7
by: JN | last post by:
Hello. Sorry about the length of this post. I am trying to implement a hash table for my chess program, and decided to try using the VC++ . NET (Dinkumware) version of hash_map. The role of the...
13
by: Michelle | last post by:
Hi all... I could use a little TLC here for understanding and not for solving a specific problem... sorry if I've got the wrong group, but I'm using VB so I figured this was the most appropriate...
17
by: homepricemaps | last post by:
hey folks, have a logic question for you. appreciate the help in advance. i am scraping 3 pieces of information from the html namely the food name , store name and price. and i am doing this...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
8
by: Jeff S | last post by:
Please note that this question is NOT about any particular pattern - but about the general objective of separating out presentation logic from everything else. I'm trying to "get a grip" on some...
1
by: Miguel Dias Moura | last post by:
Hello, I have a GridView in my page which is created in runtime. It works fine. My page has 2 Asp Buttons: - The HIDE button makes GridView.Visible = False; - The SHOW button makes...
1
by: =?ISO-8859-1?Q?Lasse_V=E5gs=E6ther_Karlsen?= | last post by:
I get the above error in some of the ASP.NET web applications on a server, and I need some help figuring out how to deal with it. This is a rather long post, and I hope I have enough details that...
14
by: Gilles Ganault | last post by:
Hi One of the ways to raise performance for PHP apps is to separate static contents from dynamic contents, so that the former can be compiled once into cache. Can someone give me a simple...
8
by: Sham | last post by:
I am trying to perform the following query on a table that has been indexed using Full Text Search. The table contains multiple columns than have been indexed. (Below, all xml columns are...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.