Table with multiple column entries listed vertically

[GirthJohnson]

I received an Excel work sheet with 60,000+ records and 9 columns. The last column is laced with multiple entries per record listed vertically which creates mostly empty rows with on cell of data. The last column should actually be four additional columns. How do I get the records with vertical entries to list horizontally into additional columns?

What I have (example)...

NAME|ADDRESS|CITY|STATE|ZIP_#|ACCOUNT_NUM
Mark|34 L St|RENO|NV   |88097|465879
Bob |12 K ST|RENO|NV   |89101|123456
    |       |    |     |     |789123
    |       |    |     |     |654321
    |       |    |     |     |321789

What I need (example)...

NAME|ADDRESS|CITY|STATE|ZIP_#|ACCOUNT_NUM|AN2   |AN3   |AN4
Mark|34 L St|RENO|NV   |88097|465879     |      |      |
Bob |12 K ST|RENO|NV   |89101|123456     |789123|789123|321789

I'm a bit of a noob and have limited experience with ACCESS VBA and SQL. Any help would be greatly appreciated. Thank you!

[Rabbit]

The way you want to store it is unnormalized and is bad form. It really should be separated out into a different table with a row for each line.

[GirthJohnson]

Rabbit,

Thanks for the response. I know its bad form (violates 2NF) and I'm trying to fix that.

I'm prepping a data file for print production. The columns will be data headers that are called in my program. However the program won't recognize multiple entries for a record that has one column listing multiple entries vertically.

Any suggestions would be greatly appreciated!

[Rabbit]

Well, in that case, after you import the data, you can use a crosstab query to denormalize the data. The specifics will depend on how you are importing the data into Access.

[GirthJohnson]

I'm using Access 2003 and importing the excel worksheet through the standard "Get External Data" and then "Import". I select first row as headers and add the additional headers I want. I let access add a primary key for each row.

Not familiar with "Crosstab" Queries. I've used the append, delete, make table but not crosstab.

[Rabbit]

A crosstab query lets you pivot the data in a field into columns. Setting one up is fairly simple in that you just have to select where the fields go. The problem is that if you are importing the data exactly as is, you will have to do some prior setup.

First, you will have to fill in the blank rows of data. If you do not know how to do that, please create a new thread for it as we try to limit threads to one question each.

Second, you will need to number the rows partitioning on your grouping. Create a new thread for that as well if you don't know how to do it.

Once you have that set up, you can set up the crosstab query fairly easily.

[GirthJohnson]

I'm looking up crosstab queries and they seem to be for aggregating data from multiple tables. I'm only using one table and I only need data from the one table.

I don't think I'm getting what I need across correctly. Thanks for your time. I'll keep researching.

[Seth Schrock]

If all you are trying to do is break up a field in Excel, then Excel has the ability to break up the text in the column either by fixed width or using a delimiter (like a comma, semicolon, etc.). I have never done this in Excel 2003 (what I'm guessing you have since that is the version of Access you have) so I'm not sure the exact process for you, but you should be able research it. In Excel 2010 it is called "Text to Columns".

[Rabbit]

I understand what your goal is, you misunderstand the possibilities of a cross tab aside from it's stated usage.

1) A crosstab in no way requires you to use more than one table. You can, and most people do, only use one table for crosstabs.

2) While a crosstab requires an aggregation to fill in the cell values, in your situation, there will only be one value per cell to aggregate, resulting in no aggregation at all.

[zmbd]

Gith,
Basically, in that last column in your data what happened was that someone entered the first value, say 123456, THEN pressed <alt><enter> to insert the new line under the first, within the same cell, instead of using commas or something else between the entries (naughty, naughty!):
Row 1 : Column A
(123456<alt><en ter>
789<alt><enter>
45674<alt><ente r>
etc...)

Now you want to take that multi-entry-cell and break it up so that one entry per cell:
.......Clmn A....B.......C. .......D......
Row1: (123456)(789)(4 5674)(etc...)

If the entries have a fixed length then you can use the Excel "text to column" under the data ribbon (same area in Excel 2003); however, from the data you provide, there isn't a fixed length so we would opt for character delineated. Then we get the other issue in that you cannot enter the equivalent of then <alt><enter> into the "other" option.

AHHH but I have the solution... mesa so smart betcha betcha:

The data is separated via the CHR$(10) character.
Now you can pull the data into Access and have fun parsing it or you can do it in a few steps within Excel:

In the cell next to the multiline (in this example case, Row 1 Column B) enter the following:
=SUBSTITUTE(A1, CHAR(10),",")
You now have a cell that looks like:
Row 1 : Column B
(123456,789,45674,etc...)

>>>EDIT<<<
-just to make the change more obvious:
(123456 , 789 , 45674 , etc...)
(note that there wouldn't be any spaces... the string will appear as in the orginal line above.
>>>EDIT<<<


Now this appears to be a nice comma delineated string; however, we're not done yet as what is actually there is the result of the formula.

Easy fix:
Select the Column B
<ctrl-C>
Right-click on cell C1 in our example
Paste Special, VALUES only.

Now we have a nice comma delineated string that we can parse into the data set or use the {text to columns} function under the data tab within excel.

[NeoPa]

Girth:
The last column is laced with multiple entries per record listed vertically which creates mostly empty rows with on cell of data.

I'm going to start with the why this time Z. I believe the quoted text implies that your understanding of what the OP has in front of them is inaccurate. If there are multiple rows with just one cell of data in them I believe that indicates Account Number values in distinct cells and records (or Rows for Excel).

Personally, I would populate the data in Excel to reproduce the lines above where blanks are found, then import that into Access. The OP has requested the format of multiple Account Number fields (columns) aggregated into the same record though.

To handle that, and assuming four is the maximum number of Account Numbers found per (logical) record, as indicated by the OP, I would use the following formulas in columns G, H & I (Enter these into cells G2 through I2 and drag down to the last row) :

=IF($E3>"",$F3,"")      =IF($E4>"",$F4,"")      =IF($E5>"",$F5,"")

After using Z's neat trick of replacing the formula cells with the values (Paste Special|Values) sort the rows on column A. Everything with no value will be buched up together and can be deleted en-block.

You should now have a file that can be imported into Access of a format you require.

[zmbd]

I'm going to start with the why this time Z. I believe the quoted text implies that your understanding of what the OP has in front of them is inaccurate

Also noted is that OP has come back and formatted the example data so that its intent is much clearer.

The way the OP was originally formatted and the manner of description in Post#3 where OP stated that they were attempting to fix a non-normalized data set, I misunderstood the question. However, at least there was something useful in my post! :)

Now if your solution in #11 is what OP desires, then wouldn't this violate the rules of normalization which is contrary to what OP stated was desired in post #3


mesa so smart betcha betcha.... oh welll.... lost that money gotcha find emes a Jedi for eaten now :)

[NeoPa]

Z:
Now if your solution in #11 is what OP desires, then wouldn't this violate the rules of normalization which is contrary to what OP stated was desired in post #3

You may want to reread post #3 smarty-pants :-D He clearly indicates an understanding of normalisation, but also recognises that sometimes other matters limit what can be done practically.

Actually, if the other form is required then I'm happy to provide a similarly practical solution, but my current understanding is that is not, even though I may recommend it.

Z:
However, at least there was something useful in my post!

You can rely on that. I do. I guess you slip up from time-to-time, but it's pretty rare and totally overshadowed by all the useful stuff in your posts. You're doing a great job - I have no doubt on that score :-)

[GirthJohnson]

Z, Neo... Thanks for the great input. I'll go back and try the method you've provided.

I consulted a friend directly and we hammered out some code in VBA to parse out the table. I imported the excel product without any tweaks and created a new form. The form has a single button (click event). Please see the VBA listed below...

Option Compare Database

Private Sub Command1_Click()

' rsOTC is recordset for the troubled excel table I've imported
Dim Ctr As Integer
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String

Dim rsOTC As New ADODB.Recordset

Set rsOTC = New ADODB.Recordset

'Open new database connection
Dim cnn5 As New ADODB.Connection

Set curdb1 = CurrentDb
Set cnn5 = New ADODB.Connection

With cnn5
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "data source=" & curdb1.Name
    .Open
End With
    
'Open troubled table "tblERMS2012"
strSQLOTC = "SELECT * from [tblERMS2012]"
rsOTC.Open strSQLOTC, cnn5, adOpenDynamic, adLockOptimistic, adCmdTxt

Do While Not rsOTC.EOF


    ' Initialize variables
    
    Ctr = 1
    str2 = ""
    str3 = ""
    str4 = ""
    str5 = ""

    Do While (IsNull(rsOTC!EIN) Or rsOTC!EIN = "")
    
               
        Ctr = Ctr + 1
    
            ''MsgBox "Made it to the counter!!!!" & Ctr
    
        Select Case Ctr
            Case 2
                str2 = rsOTC![FMLA CASE #1]
            Case 3
                str3 = rsOTC![FMLA CASE #1]
            Case 4
                str4 = rsOTC![FMLA CASE #1]
            Case 5
                str5 = rsOTC![FMLA CASE #1]

        End Select

            ''MsgBox "Made it to Case 2!!!!" & str2

        rsOTC.MoveNext
    
    Loop  ''IsNull (rsOTC!EIN)
    
    If Ctr > 1 Then
    
        For i = 1 To Ctr
        
            rsOTC.MovePrevious
        
        Next i
        
        rsOTC![FMLA CASE #2] = str2
        rsOTC![FMLA CASE #3] = str3
        rsOTC![FMLA CASE #4] = str4
        rsOTC![FMLA CASE #5] = str5

    End If
    
        For j = 1 To Ctr
        
        rsOTC.MoveNext
        
        Next j
        
Loop  ''End While Not rsOTC.EOF

            
MsgBox "Cross your fingers!!!!"

'Close all recordsets and connections
rsOTC.Close
Set rsOTC = Nothing
cnn5.Close
Set cnn5 = Nothing
End Sub

This actually worked! Depending upon your registry settings, it may generate a runtime error (MaxLocksPerFil e) but there's an easy fix listed here, http://support.microsoft.com/kb/815281

Thanks again for all the help! You guys are awesome!

[NeoPa]

Girth, it's not a good idea to throw some random extra code into a thread like this.

1. It indicates you are not focusing on the thread itself, and the suggestions contained within it.
2. It is not explained and readers of the thread cannot easily follow the logic - especially if it is not supported by any explanations as in this case.
3. It diverts the thrust of the thread into a new channel, which is exactly what no-one needs a thread to do. We try to ensure that threads stay on topic, so as to be easy to follow. This makes that extra difficult.

Please, if you have external code to introduce, and it's necessary to the thread, explain the code clearly and why you feel that. Just adding it into the mix is not helpful. I propose to ignore that part for now. If it needs to be introduced into the general thread (in this case I very much doubt it) then please do so with proper introduction and explanation.

If, on the other hand, you want some help with this code specifically then please post it in a separate thread. It will still need more explanation than you've included here, but we can then focus on that topic rather than allow it to confuse this one.