Hi, To All the GURUs on this forum. 3

suddu · Jan 22, 2004

Hi, To All the GURUs on this forum. You guys have been a great help all the way in my various projects. I am faced with a unique problem this time. I have a table full of values with approx 3000 records. What I want to do is get the data from one of the cells to seprate into as many records as seprators in that field and get the rest of the data to repeat in those records. for example

Generic Trade Name Dosage Form Usual Dose
A BAC Caps 10, 20, 50

What I want to get is:
Generic Trade Name Dosage Form Usual Dose
A BAC Caps 10
A BAC Caps 20
A BAC Caps 50

Any and all the help will be greatly appreciated, Thanks In advance

Golom · Jan 22, 2004

You need to create another table called (for example) Integers that has one field with integer values from 1 to the length of the longest string plus 2 (10 in your example). Then you can use a query like

Code:

SELECT Generic, TradeName, DosageForm, 
       Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot;, S1.num + 1 , S2.num -S1.num - 1) AS Dose

FROM Drugs AS I1, Integers AS S1, Integers AS S2

WHERE S1.Num < 11 And S2.Num < 11 
      and Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot; ,S1.num , 1 ) = ','
      and Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot; ,S2.num , 1 ) = ','
      and S1.num < S2.num
      and InStr(1,Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1), &quot;,&quot;)=0

ORDER BY Generic, TradeName, DosageForm

While that will produce the result that you want, I recommend that you think about normalizing your tables to eliminate the use of multi-valued fields.

suddu · Jan 22, 2004

Thank You GOLOM, Appreciate your quick response, but I am still not sure how to achieve this, I have the table named ExperimentalTable and added a filed to it which is know as Value. The value field is also the integer field. After making the query I get this

SELECT GenericName, TradeName, DosageForm,
Mid$("," & I1.UsualDose & ",", S1.num + 1 , S2.num -S1.num - 1) AS Dose

FROM ExperimentalTable AS I1, ExperimentalTable AS S1, ExperimentalTable AS S2

WHERE S1.Num < 11 And S2.Num < 11
and Mid$("," & I1.UsualDose & "," ,S1.num , 1 ) = ','
and Mid$("," & I1.UsualDose & "," ,S2.num , 1 ) = ','
and S1.num < S2.num
and InStr(1,Mid$("," & I1.UsualDose & ",", S1.num + 1 , S2.num - S1.num - 1), ",&quot

=0

ORDER BY GenericName, TradeName, DosageForm
I get an erroer as the GENERIC NAME COULD REFER TO MORE THAN ONE TABLE LISTED IN THE FROM CLAUSE OF YOUR SQL STATEMENT. While I donot have any table named as Generic Name its only the field name.
Pardon my ignorance.

Golom · Jan 22, 2004

I said another table. Note that in my post, the table with the range variables S1 and S2 is the Integers table ... not the table with your source data (ExperimentalTable in your example.)

The Integers Table should look like this

Code:

Num

Code:

And then your statement becomes

Code:

SELECT GenericName, TradeName, DosageForm, 
       Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot;, S1.num + 1 , S2.num -S1.num - 1) AS Dose

FROM ExperimentalTable AS I1,

Code:

Integers

Code:

 AS S1,

Code:

Integers

Code:

 AS S2

WHERE S1.Num < 11 And S2.Num < 11 
      and Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot; ,S1.num , 1 ) = ','
      and Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot; ,S2.num , 1 ) = ','
      and S1.num < S2.num
      and InStr(1,Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1), &quot;,&quot;)=0

ORDER BY GenericName, TradeName, DosageForm

You also need to change "11" in

Code:

WHERE S1.Num < 11 And S2.Num < 11

to a value that is 3 more than the length of the longest string in "UsualDose". Your Integers table also needs to have at least that many numbers in it. You can determine the length of the longest string with

Code:

Select TOP 1 Len(UsualDose) As [FieldLength]
From ExperimentalData
ORDER BY 1 DESC

suddu · Jan 22, 2004

Hi, I have everyting now setup as you said, but now the error message reads invalid procedure call.

Golom · Jan 22, 2004

The only procedures being called are "Mid" and "Instr" and the code works on my machine. Can you post the SQL you are running?

JonFer · Jan 22, 2004

It could be a Null value in UsualDose. Add a criteria to prevent Nulls and see if the error still occurs.

TheAceMan1 · Jan 23, 2004

Everyone . . . Hold it! . . . Hold it! . . . . . Hold It!

I've been monitoring this thread and found it compelling to jump in here. Although your on the right track for resolution of this problem, your taking the long way around and making it a little more complicated than necessary. No extra tables and such are needed. A simple recordset will suffice, it just has to be manipulated correctly.

suddu . . . . . here's the code:

Code:

Public Sub ExpandDose()
   Dim db As Database, rst As Recordset
   Dim oldIdx As Long, newIdx As Long
   Dim hldGen As String, hldTN As String
   Dim hldDF As String, hldUD As String
   Dim nChr As Long, SQL As String
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset(&quot;

Code:

TableName

Code:

&quot;, dbOpenDynaset)
   oldIdx = 1
   
   Do

Code:

'If comma exist continue. Skip otherwise.

Code:

      If InStr(oldIdx, rst![Usual Dose], &quot;,&quot;) Then

Code:

'Hold record common data.

Code:

         hldGen = rst!Generic
         hldTN = rst![Trade Name]
         hldDF = rst![Dosage Form]
         hldUD = rst![Usual Dose]
         
         newIdx = InStr(oldIdx, hldUD, &quot;,&quot;)

Code:

'Extract components and add Records

Code:

         Do Until InStr(oldIdx, hldUD, &quot;,&quot;) = 0

Code:

'nChr is used to prevent word wrapping
            'in the post!

Code:

 ;-)
            nChr = newIdx - oldIdx
            'Add new record
            rst.AddNew
            rst!Generic = hldGen
            rst![Trade Name] = hldTN
            rst![Dosage Form] = hldDF
            rst![Usual Dose] = Mid(hldUD, oldIdx, nChr)
            rst.Update

Code:

'Set Idx for next component

Code:

            oldIdx = newIdx + 1
            newIdx = InStr(oldIdx, hldUD, &quot;,&quot;)

Code:

'PickUp far right component when no
            'more comma's detected.

Code:

            If newIdx = 0 Then
               nChr = Len(hldUD) - oldIdx + 1
               rst.AddNew
               rst!Generic = hldGen
               rst![Trade Name] = hldTN
               rst![Dosage Form] = hldDF
               rst![Usual Dose] = Right(hldUD, nChr)
               rst.Update
            End If
         Loop
      End If
      
      rst.MoveNext
      oldIdx = 1
   Loop Until rst.EOF

Code:

'Delete all records with comma in Dose

Code:

   SQL = &quot;DELETE

Code:

[YourPrimaryKeyName]

Code:

, [Usual Dose] &quot; & _
         &quot;FROM tblTest &quot; & _
         &quot;WHERE InStr(1,[Usual Dose],&quot; & &quot;&quot;&quot;,&quot;&quot;&quot; & &quot;)>0;&quot;
   DoCmd.RunSQL SQL
         
   Set rst = Nothing
   Set db = Nothing

End Sub

Don't forget to backup the database before testing any code!

This should do it!

TheAceMan [wiggle]

TheAceMan1 · Jan 23, 2004

suddu . . . . . .

There's an error in the SQL string.

"FROM tblTest " & _
Should be
"FROM TableName " & _

Sorry bout that! . . . .

TheAceMan [wiggle]

Golom · Jan 23, 2004

TheAceMan1

That will permanently change the data in his table. If that's what he wants then it looks like a workable solution.

BTW: How is a 48-line piece of VB code a Less complicated solution than an 8-line SQL statement?

TheAceMan1 · Jan 23, 2004

How are ya Golom . . . .

First, be aware that I'm not trying to overstep you here. I infact bow on one knee to your knowledge.

According to suddu's origional post, its clear to me that he intends replace. In this light, you would require additional queries to obtain an replaced result. I started to use queries myself, but found I would have to use at least three if resolution is to replace the data. In this way the recordset gives me more direct control. Besides, it also appears to me that this will be a one-shot deal.

suddu also said: I want get the data from one of the cells to seprate into as many records as seprators in that field. Your schema does not allow for variable comma count!.

So . . . . what do you think Golom?

TheAceMan [wiggle]

BSman · Jan 23, 2004

I would suggest that you change the design of the database and break the data listed into two tables like this:

Table 1 (Drug):
DrugID (autonumber primary key column)
Generic
TradeName

Table 2 (Doses):
DrugID (integer)
Form
Dose (single number)
Unit (text)

I've added a unit column for two reasons. One is to provide a place to define what the dosage unit is (ex: milligram, gram, milliliter, picoliter). The second is to provide better data control, so dose must be a number and the Unit will be text. You can also limit the data in Unit by adding a lookup table that contains the permissible units.

The two tables will be related one-to-many (Table1 to Table 2) by DrugID.

This design provides maximum flexibility and greatly minimizes errors in data entry. You also can easily add a variety of combinations of forms as well as dosages for a specific drug (ex: capsule, liquid, tablet).

suddu · Jan 23, 2004

Thank You All:

But I am still getting an error when I list the code and modify according to my tables

Gives me an error as "User -Defined type not defined" Compile error with Expand_DosageForm in yellow and db as Databse in blue. Here is what I entered.

Public Sub Expand_DosageForm()
Dim db As Database, rst As Recordset
Dim oldIdx As Long, newIdx As Long
Dim hldGen As String, hldTN As String
Dim hldDF As String, hldUD As String
Dim nChr As Long, SQL As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("DRUGS", dbOpenDynaset)
oldIdx = 1

Do
'If comma exist continue. Skip otherwise.
If InStr(oldIdx, rst![Dosage Form], ",&quot

Then
'Hold record common data.
hldGen = rst!Generic
hldTN = rst![Trade Name]
hldDF = rst![Usual Dose]
hldUD = rst![Dosage Form]

newIdx = InStr(oldIdx, hldUD, ",&quot

'Extract components and add Records
Do Until InStr(oldIdx, hldUD, ",&quot

= 0
'nChr is used to prevent word wrapping
'in the post!
nChr = newIdx - oldIdx
'Add new record
rst.AddNew
rst!Generic = hldGen
rst![Trade Name] = hldTN
rst![Usual Dose] = hldDF
rst![Dosage Form] = Mid(hldUD, oldIdx, nChr)
rst.Update

'Set Idx for next component
oldIdx = newIdx + 1
newIdx = InStr(oldIdx, hldUD, ",&quot

'PickUp far right component when no
'more comma's detected.
If newIdx = 0 Then
nChr = Len(hldUD) - oldIdx + 1
rst.AddNew
rst!Generic = hldGen
rst![Trade Name] = hldTN
rst![Usual Dose] = hldDF
rst![Dosage Form] = Right(hldUD, nChr)
rst.Update
End If
Loop
End If

rst.MoveNext
oldIdx = 1
Loop Until rst.EOF

'Delete all records with comma in Dose
SQL = "DELETE [ID], [Dosage Form] " & _
"FROM DRUGS " & _
"WHERE InStr(1,[Dosage Form]," & """,""" & &quot

>0;"
DoCmd.RunSQL SQL

Set rst = Nothing
Set db = Nothing

End Sub

Golom · Jan 23, 2004

TheAceMan1
Not a problem ... the more input the better. I just couldn't see your point on the complexity issue.

If replacement is the objective then your code is probably the way to go ... with a qualifier ... that being that we don't know that the fields he has placed in his query are all the fields in the table. Minor modification required to handle that however.

On the second point ... variable number of commas ... Yes. The query does handle that. Here's the breakdown of the SQL

THE JOIN
Suppose we had a field like "a,b" and Integers 1-5. The join operation then builds an intermediate result like

Code:

String S1 S2

Code:

,a,b,      1     1
,a,b,      1     2
,a,b,      1     3
etc.
,a,b,      5     1
,a,b,      5     2
etc.
,a,b,      5     5

THE WHERE CLAUSE
It then eliminates all those records where the SubStrings are not commas, S2 <= S1 or there is a comma embedded between two other commas (i.e. the Instr) leaving

Code:

String S1 S2

Code:

,a,b,      1     3
,a,b,      3     5

THE SELECT CLAUSE
The individual record values are then extracted as the substrings between commas with

Code:

Mid$(&quot;,&quot; & I1.UsualDose & &quot;,&quot;, S1.num + 1 , S2.num-S1.num-1) AS Dose

As you can see, this same pattern applies regardless of the number of commas (as long as you have Len+2 integers available). For example, with "a,b,c,d" the intermediate extraction after WHERE would yield

Code:

String S1 S2

Code:

,a,b,c,d,      1     3
,a,b,c,d,      3     5
,a,b,c,d,      5     7
,a,b,c,d,      7     9

Golom · Jan 23, 2004

suddu
Go into any form in your project and select "Tools / References" from the menu bar. In the list of referemces, find "Microsoft DAO 3.x Object Library" (x may be 51 or 4) and place a check merk on it.

suddu · Jan 23, 2004

Thanks Golom, It works there now but now the error has moved to Line 10 of the code, as RunTime error 13, Type mismatch with the line

Set rst = db.OpenRecordset("DRUGS", dbOpenDynaset)

highlighted

the table name is DRUGS, the fields are GenericName, TradeName, DosageForm, and UsualDose, ID;

The DosageForm has the long strings with various dosages that needs to be seprated into various rows with the corresponding data duplicated in those rows.

Thanks

TheAceMan1 · Jan 23, 2004

Roger that Golom!

I reread the query and your right. Also good pickup on the DAO library reference.

I guess it depends now on what suddu posts . . . . . .

TheAceMan [wiggle]

Golom · Jan 23, 2004

Change

Code:

Dim db As Database, rst As Recordset

To

Code:

Dim db As DAO.Database, rst As DAO.Recordset

The default in Access2K and above is ADO so its picked up the ADO recordset rather than the DAO one that you need.

TheAceMan1 · Jan 23, 2004

suddu . . . . .

First you need to correct all the field names in brackets. I origionally copied them from your origional post. Go through and remove the brackets and space. This may be a precursor to the error. Let us know . . . .

TheAceMan [wiggle]

TheAceMan1 · Jan 23, 2004

suddu . . . .

Also go back to the DAO 3.x library reference and push it as high as it will go in priority . . . . .

TheAceMan [wiggle]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Hi, To All the GURUs on this forum. 3

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor