Create new records by splitting text field (Proj1,Proj2) 2

[Poduska]

I need to create copies of a record for each instance of a value (Project) found inside of one field within the record.

I realize this is lousy design but I have to utilize a table I cannot change.

I have a table with a fields called Document and PROJ.
PROJ lists the projects for which a (Document)record applies.

So my record looks like this:

Document     PROJ
XYZ          "807A,525,825,545"

Proj can have one or multiple values as shown above and the number of strings within PROJ will vary.

I need to have 4 records:

Document  Project
    XYZ   807A
    XYZ   525
    XYZ   825
    XYZ   545

I thought I had a solution by doing a cartisian join against a query of ALL the possible Project codes 807A,525 etc. called qryProjectCodes and then using this formula

Exists if > Zero: InStr(1,[qryDocument]![proj],[qryProjectCodes]![ProjectCode])

and using a criteria of >0

So for each record of the many to many query I check to see if the Project from my master list resides in the PROJ field and if so the "Exists if" statement returns a value greater than zero and I keep the record.

This worked fine [red]UNTIL[/red] I have a string in my PROJ field such as 807A and values in my list of all possible projects (qryProjectCodes) of 807 and 807A. In this situation I am returning TWO records "807" (error) and 807A (expected)as 807 is found in the string 807A.


[RED]How can I examine the strings as entire values as they are separated by commas from my PROJ fields against another entire field value.?[/RED]

 

[Remou]

Does the answer have to be pure SQL or is VBA acceptable?

http://lessthandot.com

 

[dhookom]

I would create a table of every unique Proj value.
tblProjs
============
Project

You can then create a cartesian query with the proj table and existing table/query. Use a field in the query grid of:
MultiProj: "," & [Proj] & ","
Set the criteria under this column to
Like "*," & [Project] & ",*"
Add the Project and Document fields to the query and you should end up with multiple records.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

I wonder will this be a problem with 807 and 807A in that

Like *807*

Includes 807A?

http://lessthandot.com

 

[Poduska]

Remou, I can use SQL or VBA to derive the result entirely up to how I can get it to work.
I just arrived home and have made a sample DB to try dhookom's suggestion with only one * as to the best of my knowledge the projects are 3 numbers followed by a possible single text character.

Will follow up after I try the suggestion.

Thanks for any help, hope my description was enough to go on.

 

[Remou]

VBA may be the safest option, though possibly slow, roughly:

CurrentDB.Execute "CREATE TABLE tblNew (Document Text(10), Proj Text(10))"
strSQL="SELECT Document, Proj FROM table"
Set rs=CurrentDB.OpenRecordset(strSQL)

Do While Not rs.EOF
   astrProj=Split(rs!Proj,",")
   
   For i=0 To UBound(astrProj)
      strSQL="INSERT INTO tblNew (Document, Proj) Values ('" _
        & rs!Document & "','" & astrProj(i) & "')"
      CurrentDB.Execute strSQL, dbFailOnError
    Next
    
    rs.MoveNext
Loop

http://lessthandot.com

 

[Poduska]

IT WORKED, just as dhookom suggested.

Thank you all very much.

As I don't have a good way to permanantly post the sample database. Here are the details from my simple "test the concept" database in hope that everyone's work will help others.

[red]tblDNote[/red]The Document table

txtDocument	txtPROJ
A	525,666,807,807A,405,405X
B	525,666,807,807A,405
C	807A,405,405X
D	807,405
E	405X,807A
F	807
G	525
H	807A
I	405
J	405X
K	405X,405,807A,807,666,525
L	405,807A,807,666,525
M	405X,405,807A

[red]tblProject[/red]

txtProject
405
405X
525
666
807
807A

[red]Here is the Query SQL[/red]

SELECT tblDNote.txtDocument, tblDNote.txtPROJ, tblProjects.txtProject, "," & [tblDNote]![txtPROJ] & "," AS MultiProj
FROM tblDNote, tblProjects
GROUP BY tblDNote.txtDocument, tblDNote.txtPROJ, tblProjects.txtProject
HAVING ((("," & [tblDNote]![txtPROJ] & ",") Like "*," & [txtProject] & ",*"))
ORDER BY tblDNote.txtDocument, tblDNote.txtPROJ, tblProjects.txtProject;

[red]And here is the results of the query.[/red]
Long but I tried hit all posibilities.

txtDocument	txtPROJ	txtProject	MultiProj
A	525,666,807,807A,405,405X	405	,525,666,807,807A,405,405X,
A	525,666,807,807A,405,405X	405X  ,525,666,807,807A,405,405X,
A	525,666,807,807A,405,405X	525	,525,666,807,807A,405,405X,
A	525,666,807,807A,405,405X	666	,525,666,807,807A,405,405X,
A	525,666,807,807A,405,405X	807	,525,666,807,807A,405,405X,
A	525,666,807,807A,405,405X	807A	,525,666,807,807A,405,405X,
B	525,666,807,807A,405	     405	,525,666,807,807A,405,
B	525,666,807,807A,405	     525	,525,666,807,807A,405,
B	525,666,807,807A,405	     666	,525,666,807,807A,405,
B	525,666,807,807A,405	     807	,525,666,807,807A,405,
B	525,666,807,807A,405	     807A	,525,666,807,807A,405,
C	807A,405,405X	            405	,807A,405,405X,
C	807A,405,405X	            405X	,807A,405,405X,
C	807A,405,405X	            807A	,807A,405,405X,
D	807,405	                  405	,807,405,
D	807,405	                  807	,807,405,
E	405X,807A	                405X	,405X,807A,
E	405X,807A                    807A	,405X,807A,
F	807	                      807	,807,
G	525	                      525	,525,
H	807A	                     807A	,807A,
I	405	                      405	,405,
J	405X	                     405X	,405X,
K	405X,405,807A,807,666,525	405	,405X,405,807A,807,666,525,
K	405X,405,807A,807,666,525	405X	,405X,405,807A,807,666,525,
K	405X,405,807A,807,666,525	525	,405X,405,807A,807,666,525,
K	405X,405,807A,807,666,525	666	,405X,405,807A,807,666,525,
K	405X,405,807A,807,666,525	807	,405X,405,807A,807,666,525,
K	405X,405,807A,807,666,525	807A	,405X,405,807A,807,666,525,
L	405,807A,807,666,525	     405	,405,807A,807,666,525,
L	405,807A,807,666,525	     525	,405,807A,807,666,525,
L	405,807A,807,666,525	     666	,405,807A,807,666,525,
L	405,807A,807,666,525	     807	,405,807A,807,666,525,
L	405,807A,807,666,525	     807A	,405,807A,807,666,525,
M	405X,405,807A	            405	,405X,405,807A,
M	405X,405,807A	            405X	,405X,405,807A,
M	405X,405,807A	            807A	,405X,405,807A,

 

[dhookom]

I wonder will this be a problem with 807 and 807A in that

Like *807*

Includes 807A?

That's why I suggested adding the extra commas to the query.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

[Blush] Note to self, read more carefully late at night

http://lessthandot.com