Extracting Data into a New field

  • Thread starter Thread starter MissAmanda
  • Start date Start date
M

MissAmanda

Guest
You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:

Field 1

DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5

I need it to read like this:

Field 1 Field 2

DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5

If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.

Thanks,
Amanda
 
Last edited:
Hi Amanda -

Two ideas come to mind.

1. Write some VBA code to do this
It looks like the order of the records is important: e.g. all the items after DMA Albany should "remember" that field until the next DMA item. A VBA procedure could scan through the records in order and build the appropriate strings. If you have some familiarity with VBA, then this could be done with out too much effort.

2. Do it in Excel
It is very easy to generate the required fields in Excel (okay, the easiest way also generates some bogus records, but these can easily be weeded out). The only question I have about this is whether it is managable given the number of records you have. See attached for an example.

Someone else might have a slicker approach than this in Access.

- g
 

Attachments

The attached example is a great start. Thank you for giving your time. With the large data set (it ranges from the 5000s-10000s), it's not 100% ideal. However, it may just have to work for now. I'm not familiar with VBA programming, but I understand the logic behind your idea. Do you have any suggestions? Is this a question that should be posted on another board perhaps? Thanks again.

Amanda
 
Amanda -

See the attached database. There are two tables. The first, tblData, should look like what your raw data is. The second, tblOutput, should be blank initially, but will contain the results of what we put in there.

There is also a form. Open the form and click the button. Then go check the output table. The VBA behind the button should have filled the table with the data in the correct format.

The VBA code is
Code:
Private Sub btnCalculate_Click()

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsDestination As DAO.Recordset
    Dim strSQL As String

    Dim strDMAField As String
    Dim lngDMACount As Integer
    Dim lngTotalCount As Integer

    
    ' Set variable to this database
    Set db = DBEngine(0)(0)
    
    ' Empty the temporary table
    db.Execute "DELETE * FROM tblOutput", dbFailOnError
        
    ' Open a record set for the source
    strSQL = "SELECT * FROM tblData"
        
    Set rsSource = db.OpenRecordset(strSQL, dbOpenDynaset)
        
    'Open a recordset for the destination
    Set rsDestination = db.OpenRecordset("tblOutput", dbOpenTable)
        
    ' Zero the total count
    lngTotalCount = 0
        
    ' Execute loop for all included records
    While Not rsSource.EOF
    
        ' Check Field1 for current source record
        If (Left(rsSource!Field1, 3) = "DMA") Then
            ' Start a new DMA
            
            strDMAField = rsSource!Field1
            lngDMACount = 1
        Else
            
            ' Write a record to the destination table
            With rsDestination
                .AddNew
                !Field1 = strDMAField
                !Field2 = rsSource!Field1
                !DMACount = lngDMACount
                .Update
            End With
            
            lngDMACount = lngDMACount + 1
            
        End If
    
        rsSource.MoveNext
        lngTotalCount = lngTotalCount + 1
        
        ' Check if user wants to bail after a certain amount of records
        If (lngTotalCount Mod 1000 = 0) Then
            If MsgBox(lngTotalCount & " Records so far.  Continue? ", vbYesNo) = vbYes Then
                ' Continue
            Else
                ' Bail
                GoTo CloseExit
            End If
        End If
    Wend
        
    ' Print some debug values to the immediate window - this can be eliminated.
    Debug.Print strDMAField, lngDMACount, lngTotalCount
        
CloseExit:

    ' Close up and quit
    rsSource.Close
    Set rsSource = Nothing
    
    rsDestination.Close
    Set rsDestination = Nothing
    
    Set db = Nothing
    
End Sub

I've tried to document this reasonably well, but I'm not sure how much makes sense. If you have questions, ask.

A few other things. If the input table is long, the VBA will periodically ask if you want to keep continuing. This is so you can easily abort if something isn't right, and lets you know how things are progressing. I don't really know how fast this will work on a large input table. I'd try it with 1000 - 5000 records first and see.

Also, every time that this VBA runs, it deletes any existing records in the output table. Finally, if you try this in another database, you will need to make sure that the DAO Object library is included in the references.

Hope that this isn't too confusing.

- g
 

Attachments

Users who are viewing this thread

Back
Top Bottom