Array from an Array

[GISnunavut]

All:

My first time posting. I'm using DAO in MS Access 2003.

Here's the story...I have a table called Collection_Step_Two with 8 columns and 12546 records. I'm attempting to create another table "Collection_Step_Three" from all data in "Collection_Step_Two".

However, in the source table, the column "Last_Name" is complex. This column is String type data. This column has many types of string values - NULL, proper "lastnames" and the following type (delimited with "@"s and "%"):

lastname@firstname@organization@%lastname2@firstname2@organization2@

For example : Bertulli@Margaret@@%Woodman@D.@@

What I need help with is to get the code to accept Null string and populate the destination table, accept and populate single instance lastnames into the desination table(ie. "Smith"), and finally, use the 2 delimiters to split the strings into new records (%) and then split each new record into the lastname, first name and organization
columns respectively.

Here is the code

Function LastName_Split()

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim strRst As String
    
    Set dbs = CurrentDb
    
    strRst = "SELECT Collection_Step_Two.Borden_Number, Collection_Step_Two.Collection_Event, Collection_Step_Two.Collection_Year, " & _
             "Collection_Step_Two.Collection_Title, Collection_Step_Two.Permit_Number, Collection_Step_Two.Last_Name, " & _
             "Collection_Step_Two.First_Name, Collection_Step_Two.Organization " & _
             "FROM Collection_Step_Two"
             
    Set rst = dbs.OpenRecordset(strRst)
    
    Dim bn_temp As String
    Dim collection_eventxxx As Long
    Dim collection_yearxxx As String
    Dim collection_titlexxx As String
    Dim permit_numberxxx As String
    Dim last_namexxx As String
    Dim first_namexxx As String
    Dim organizationxxx As String
    
    Dim LastNameArray() As String
    Dim LastNameArray2() As String
    Dim intLoopCounter As Integer
    
    rst.MoveFirst
    
    Do Until rst.EOF
        bn_temp = rst(0)
        collection_eventxxx = rst(1)
        collection_yearxxx = rst(2)
        collection_titlexxx = rst(3)
        permit_numberxxx = rst(4)
        last_namexxx = rst(5)
        first_namexxx = rst(6)
        organizationxxx = rst(7)
        
        LastNameArray() = Split(last_namexxx, "%")
        
        For intLoopCounter = 0 To UBound(LastNameArray)
            
            Dim intLoopCounter2 As Integer
            LastNameArray2 = Split(LastNameArray(intLoopCounter), "@")
            
                For intLoopCounter2 = 0 To UBound(LastNameArray2)
                    
                    Dim rst2 As DAO.Recordset
                    Set rst2 = dbs.OpenRecordset("Collection_Step_Three", dbOpenDynaset)
                    
                    With rst2
                        .AddNew
                            !Borden_Number = bn_temp
                            !collection_event = collection_eventxxx
                            !collection_year = collection_yearxxx
                            !collection_title = collection_titlexxx
                            !permit_number = permit_numberxxx
                            !permit_holder_id = intLoopCounter2 + 1
                            !last_name = LastNameArray2(0)
                            !first_name = LastNameArray2(1)
                            !organization = LastNameArray2(2)
                        .Update
                        rst2.Close
                        Set rst2 = Nothing
                    End With
                
                Next intLoopCounter2
        
        Next intLoopCounter
        
'    bn_temp = ""
'    collection_eventxxx = ""
'    collection_yearxxx = ""
'    information_temp = ""
    
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    MsgBox "Last Name Splitting Complete"
End Function

 

[kjv1611]

Well, I think you may can solve your issue a little simpler.

It sounds to me like you want to divide the data into 3 separate tables, depending upon the contents in the lastname field. If so, we can do that with 3 queries in Access.

The queries could be something like:

INSERT INTO NullNames
SELECT *
FROM OriginalTable o
WHERE o.LastName Is Null

Another would be:
INSERT INTO AtNames
SELECT *
from OriginalTable o
WHERE (INSTR(....

And last you could just JOIN against the other 2 queries, and only select values not in the other 2 queries..

I can't quite get the InStr part correct in the second query, and can't remember how to make it run in an Access query. Of course, if you preferred, you could still do the same thing as this in your VBA Recordset usage, but the other method should be more efficient.


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And this link will tell you how to use the InStr function:

http://www.techonthenet.com/access/functions/string/instr.php

--

"If to err is human, then I must be some kind of human!" -Me

 

[GISnunavut]

kjv1611:

Thanks for this. However, for reasons down the road (processing-wise) I need all records in the new table...

Let me explain it alittle better:

Right now there are 12546 records; of those, the LAST_NAME column has 45 NULL records, 11732 records have "Smith" or "Jones" or "Arundale" or etc., and 769 records have "lastname@firstname@organization@" or "lastname@firstname@organization@%lastname2@firstname2@organization2@" or etc. The "%" divides the individualsand the "@" divides the individual information.

Basically, for each "%" in the source string a new record will be created in the destination table, and the "@" will help assign the string values into the proper columns in the destination table:

Source Table

Last_Name First_Name Organization
Jones [null] [null]
[null] [null] [null]
Ross@J.@IHT@%Hotson@@@ [null] [null]

Destination Table

Last_Name First_Name Organization
Jones [null] [null]
[null] [null] [null]
Ross J. IHT
Hotson [null] [null]


As a result, the destination table will have 12546 records + the number of new records created from the "%" delimiters; 15000 records or so

Maybe this makes more sense...

Thanks.

 

[GISnunavut]

Let me repost the code...

Function LastName_Split()

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim strRst As String
    
    Set dbs = CurrentDb
    
    strRst = "SELECT Collection_Step_Two.Borden_Number, Collection_Step_Two.Collection_Event, Collection_Step_Two.Collection_Year, " & _
             "Collection_Step_Two.Collection_Title, Collection_Step_Two.Permit_Number, Collection_Step_Two.Last_Name, " & _
             "Collection_Step_Two.First_Name, Collection_Step_Two.Organization " & _
             "FROM Collection_Step_Two"
             
    Set rst = dbs.OpenRecordset(strRst)
    
    Dim bn_temp As String
    Dim collection_eventxxx As Long
    Dim collection_yearxxx As String
    Dim collection_titlexxx As String
    Dim permit_numberxxx As String
    Dim last_namexxx As String
    Dim first_namexxx As String
    Dim organizationxxx As String
    
    Dim LastNameArray() As String
    Dim LastNameArray2() As String
    Dim intLoopCounter As Integer
    
    rst.MoveFirst
    
    Do Until rst.EOF
        bn_temp = rst(0)
        collection_eventxxx = rst(1)
        collection_yearxxx = rst(2)
        collection_titlexxx = rst(3)
        permit_numberxxx = rst(4)
        last_namexxx = rst(5)
        first_namexxx = rst(6)
        organizationxxx = rst(7)
        
        LastNameArray() = Split(last_namexxx, "%")
        
        For intLoopCounter = 0 To UBound(LastNameArray)
            
            Dim intLoopCounter2 As Integer
            LastNameArray2 = Split(LastNameArray(intLoopCounter), "@")
            
                For intLoopCounter2 = 0 To UBound(LastNameArray2)
                    
                    Dim rst2 As DAO.Recordset
                    Set rst2 = dbs.OpenRecordset("Collection_Step_Three", dbOpenDynaset)
                    
                    With rst2
                        .AddNew
                            !Borden_Number = bn_temp
                            !collection_event = collection_eventxxx
                            !collection_year = collection_yearxxx
                            !collection_title = collection_titlexxx
                            !permit_number = permit_numberxxx
                            !permit_holder_id = intLoopCounter2 + 1
                            !last_name = LastNameArray2(0)
                            !first_name = LastNameArray2(1)
                            !organization = LastNameArray2(2)
                        .Update
                        rst2.Close
                        Set rst2 = Nothing
                    End With
                
                Next intLoopCounter2
        
        Next intLoopCounter
        
'    bn_temp = ""
'    collection_eventxxx = ""
'    collection_yearxxx = ""
'    information_temp = ""
    
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    MsgBox "Last Name Splitting Complete"
End Function

 

[MazeWorX]

when dealing with Nulls you can use the Nz function

eg last_namexxx = Nz(rst(5), "")

The Nz funtion acts like an IIF ... If rst5 isnull then "" else = rst5. The "" could be anything you want, you could assign a numeric value or a string. in this case an empty string.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

I see what you are doing, and a few posts back demonstrated almost the exact same thing to help someone normalize a table. Yours is a little more complicated because within a record you have information for both multiple fields, and multiple records. It would make this far simpler to do it in several steps (although it can be done in a single sub routine).

1) run an append query that handles all the instances where the name is null
2) run an Append query where the name does not have a % or @
3) build a query that returns all names with a @ but not %
run code like you are doing using that query as an input
4) build a query that returns the records with % in it
run code to insert those.

Trying to build a single loop that handles
null names
single names
compound record information
compound name, compound records

is doable, but a lot easier to handle the for case seperately

 

[ClulessChris]

Must this be done via code?
Very easy using a make table query in native access

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[GISnunavut]

Thanks All;

I used MajP's advice; Access query for the null strings and names without % and @, then slightly modified the subroutine to get the name with % and @. Then ran an append query on all.

 

[MajP]

Here is the thread I was referrring to. Thread702-1569351. Basically does the same technique as you are employing. It is not as complicated because a field can contain information about multiple records, but in your case you have a single field that contains information about multiple records and about multiple fields for each record.