Split field to create multiple records 2

[MA04]

Hi,

I am trying to split a field from a query to create multiple records. I am trying to run a search query that searches a field postcode and finds all addresses under it but they are all in one field like so:

PRMF
Craven House; Ample Mansion; WHSmith; Bulevard Terrace.

I would like to split them by the semicolom ";" so they are seperate records like:

PRMF
Craven House
Ample Mansion
WHSmith
Bulevard Terrace.

Any help very much appreciated, Thanks in advance.

 

[Golom]

Here's a way

First create a table called "Integers" with one field called "num" (Type Integer). Populate that table with the integers from 1 to two more than the length of the longest string on the PRMF field.

Then

SELECT 
   Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1) AS [SubString]

FROM tbl AS I1, integers AS S1, integers AS S2

WHERE 
   Mid$(";" & I1.PRMF & ";" ,S1.num , 1 ) = ';'
   and Mid$(";" & I1.PRMF & ";" ,S2.num , 1 ) = ';'
   and S1.num+1 < S2.num
   and instr(1,Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0;

You can include other fields as necessary in the "SELECT" clause.

 

[MA04]

Thanks for the reply, i am a little confused did you want me to create one field called: num that is of type number? (as integer is not there) also can i use a general auto number id (i.e. default) as p.k.

Thanks,
M-.

 

[lespaul]

1. Create a new table
2. Add a single field
Name = 'Num'
Type = Integer
3. Save table; Name = 'Integers'
4. Add data to Num Field.
first record num = 1
second record num = 2
....
continue until you have a record that matches the longest string in the field;
For instance, if the longest string in the field is:
ABC, DEFG, HIJKLM, NOPQ, RSTUV, WXYZ
Then you would need to have records up to 36 for the 26 letters in the string plus the commas and the spaces in between.
5. Paste the query above into a new query.
6. Replace:
FROM tbl
with your original table name
7. Run the query.

Leslie

 

[MA04]

Hi,

I have created a table called Integers and have created a field num(number)which i have populated with numbers larger then the length of the largest string in the PRMF field, but not sure how to put syntax above to my original query whi is:

SELECT
ma_dbo_uk.ID, ma_dbo_uk.postcode, ma_dbo_uk.PRMF, ma_dbo_uk.STR, ma_dbo_uk.CNT
FROM ma_dbo_uk
WHERE (((ma_dbo_uk.postcode) Like [Enter Postcode:]));

p.s. ma_dbo_uk is the table that holds PRMF Field.

Thanks in advance,
M-.

 

[Golom]

When you are creating the "Integers" table the data type of the "num" field is "number" and, if you look at the lower part of the screen, the top property is "format". Using the drop down combo box for "format" you should select "integer" (or "long integer" ... it really doesn't matter unless you have strings longer than 32768 characters.)

If you use Autonumber you need to be careful because an autonumber field will have gaps in the sequence if you ever delete records. Just make the "num" field your primary key and Access won't be "helpful" and volunteer to create a PK for you.

 

[Golom]

It should be something like

SELECT I1.ID, I1.postcode, 
   Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1) AS [SubString], I1.STR, I1.CNT

FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2

WHERE I1.postcode Like [Enter Postcode:] AND
   Mid$(";" & I1.PRMF & ";" ,S1.num , 1 ) = ';'
   and Mid$(";" & I1.PRMF & ";" ,S2.num , 1 ) = ';'
   and S1.num+1 < S2.num
   and instr(1,Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0;

 

[MA04]

Thank You guys Vey Much, it works like a charm,

M-.

 

[MA04]

Quick question guys,

If i wanted to append that field created (SubString) to a field in another table what would i call it as?

Thanks again.
M-.

 

[Golom]

I'm not exactly sure what you mean but is it something like this?

Select T.ID, (T.fld2 & X.SubString) As [ConCatField]

From OtherTbl As T INNER JOIN 

(

SELECT I1.ID, I1.postcode, 
   Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1) AS [SubString], I1.STR, I1.CNT

FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2

WHERE I1.postcode Like [Enter Postcode:] AND
   Mid$(";" & I1.PRMF & ";" ,S1.num , 1 ) = ';'
   and Mid$(";" & I1.PRMF & ";" ,S2.num , 1 ) = ';'
   and S1.num+1 < S2.num
   and instr(1,Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0 ) As X

ON T.ID = X.ID

 

[MA04]

Thanks again for the help, it wasn't what i was after, i wanted to run the append query from a command button THAT I had in a form i.e. placing it in VBA. what happens is i already perform the search that splits the field on my form and when the user selects his choice i.e. address, he enters persons name and appends that record to another table.

This is what i had, but it has a runtime error 3296, join not supported:

Private Sub Command18_Click()

Dim strSQL As String

strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT ma_dbo_uk.postcode, ma_dbo_uk.SubString, ma_dbo_uk.STR, ma_dbo_uk.CNT " & _
"FROM Integers, ma_dbo_uk LEFT JOIN ma_enq ON ma_dbo_uk.postcode = ma_enq.A4 " & _
"WHERE (((ma_dbo_uk.postcode)=[Forms]![Ma_search2]![PRMF_a1]));"

DoCmd.RunSQL strSQL

End Sub

Hope thats clearer, any help agin is very much appreciated.
M-.

 

[MA04]

Hi,

I want this SQL statement in format as the previous thread:

INSERT INTO ma_enq ( A4, A1, A2, A3 )
SELECT I1.postcode, Mid$(";" & I1.PRMF & ";",S1.num+1,S2.num-S1.num-1) AS SubString, I1.STR, I1.CNT
FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2
WHERE (((I1.postcode)=[Forms]![Ma_search2]![PRMF_a1]) AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));

The ";" do not seem to allow me to put it into VBA. p.s. I already run the query that splits the field, but i need to be able to append the result, but allow the user to select the substring he wants to append.

Any help appreciated.
M-.

 

[PHV]

Have you tried this ?
strSQL = "INSERT INTO ma_enq (A4,A1,A2,A3)" _
& " SELECT I1.postcode,Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1),I1.STR,I1.CNT" _
& " FROM ma_dbo_uk AS I1,integers AS S1,integers AS S2" _
& " WHERE I1.postcode=[Forms]![Ma_search2]![PRMF_a1]" _
& " AND Mid(';' & I1.PRMF & ';',S1.num,1)=';'" _
& " AND Mid(';' & I1.PRMF & ';',S2.num,1)=';'" _
& " AND S1.num+1<S2.num" _
& " AND InStr(1,Mid(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1),';')=0"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244