Hi, To All the GURUs on this forum. 3

[suddu]

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]

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

SELECT Generic, TradeName, DosageForm, 
       Mid$("," & I1.UsualDose & ",", 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]

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$(&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
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]

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

Num

 1
 2
 3
 4
 :
10
11
etc.

And then your statement becomes

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

FROM ExperimentalTable 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 GenericName, TradeName, DosageForm

You also need to change &quot;11&quot; in

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

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

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

 

[suddu]

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

 

[Golom]

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

 

[JonFer]

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

 

[TheAceMan1]

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:

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("

TableName

", dbOpenDynaset)
   oldIdx = 1
   
   Do

'If comma exist continue. Skip otherwise.

      If InStr(oldIdx, rst![Usual Dose], ",") Then

'Hold record common data.

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

'Extract components and add Records

         Do Until InStr(oldIdx, hldUD, ",") = 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![Dosage Form] = hldDF
            rst![Usual Dose] = Mid(hldUD, oldIdx, nChr)
            rst.Update

'Set Idx for next component

            oldIdx = newIdx + 1
            newIdx = InStr(oldIdx, hldUD, ",")

'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![Dosage Form] = hldDF
               rst![Usual Dose] = 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

[YourPrimaryKeyName]

, [Usual Dose] " & _
         "FROM tblTest " & _
         "WHERE InStr(1,[Usual Dose]," & """,""" & ")>0;"
   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

 

[TheAceMan1]

suddu . . . . . .

There's an error in the SQL string.

&quot;FROM tblTest &quot; & _
Should be
&quot;FROM TableName &quot; & _

Sorry bout that! . . . .

TheAceMan

 

[Golom]

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]

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

 

[BSman]

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]

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 &quot;User -Defined type not defined&quot; 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(&quot;DRUGS&quot;, dbOpenDynaset)
oldIdx = 1

Do
'If comma exist continue. Skip otherwise.
If InStr(oldIdx, rst![Dosage Form], &quot;,&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;,&quot

'Extract components and add Records
Do Until InStr(oldIdx, hldUD, &quot;,&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;,&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 = &quot;DELETE [ID], [Dosage Form] &quot; & _
&quot;FROM DRUGS &quot; & _
&quot;WHERE InStr(1,[Dosage Form],&quot; & &quot;&quot;&quot;,&quot;&quot;&quot; & &quot>0;&quot;
DoCmd.RunSQL SQL

Set rst = Nothing
Set db = Nothing

End Sub

 

[Golom]

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 &quot;a,b&quot; and Integers 1-5. The join operation then builds an intermediate result like

String   S1   S2

,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

String   S1   S2

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

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

Mid$("," & I1.UsualDose & ",", 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 &quot;a,b,c,d&quot; the intermediate extraction after WHERE would yield

String       S1   S2

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

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

 

[suddu]

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(&quot;DRUGS&quot;, 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]

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

 

[Golom]

Change

Dim db As Database, rst As Recordset

To

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]

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

 

[TheAceMan1]

suddu . . . .

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




TheAceMan