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
20 1784 @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: - 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.
@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
@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.
@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 32,556
Expert Mod 16PB
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
Sorry NeoPa the data is in the same field (unfortunately)
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?
the data i submitted was an extract from just one field
[Line Description]
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.
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 : - 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
That would work, hope your head is OK
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))
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.
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 32,556
Expert Mod 16PB @Dan2kx
I rather assumed that may be possible, hence the InStr() instead of the simpler Mid() function usage.
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.
@NeoPa
not sure i follow? that would organise the data incorrectly?
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 : - 442092 - B D BACTEC PLUS AEROBIC/F MEDIUM VIAL - BOX OF 50
The code using InStr() works correctly of course.
Sorry my turn for the head smacking
Thanks for the help
NeoPa 32,556
Expert Mod 16PB
Be gentle with yourself. A damaged head will only cause you more confusion :D
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |