Can you create a crosstab like query but return all rows? 1

[jeanna143]

I have a table that is a repetitive list of data. Instead of having a column with rows of data underneath we have Column A listing over and over what should be the column headings and then the corresponding data is in Column C.

For Example:
Name John Doe
Address 1234 street
***
Name Jane Doe
Address 1235 Street
***

What I need to do is Make the table look like this:
Name Address
John Doe 1234 Street
Jane Doe 1235 Street

I tried using a CrossTab query and a Pivot table but they only will return either a count or the first or last row, etc. How can I convert this table and return all rows?

Thank you!!
Jeanna

 

[Tranman]

Jeanna,
Here is a little snippet that will do what you want.

Make a copy of your current table called "OldTable".

Then create a new table (called NewTable)that has columns for all of your data items (Name, Address, Zip, whatever). The column names MUST be spelled the same as the corresponding data items in column A, and each unique item in column A MUST have a corresponding column in NewTable.

Then just paste the function below into a module and run it by putting your cursor in the code and pressing the <F5> key.

One other possible gotcha. This code uses the ADO (ActiveX Data Objects), and if you are running Access 97, you will need to put a reference to the &quot;Microsoft ActiveX Data Objects ?? Library&quot; in your project. (Just open the module, then go TOOLS | REFERENCES then put a check beside the &quot;Microsoft ActiveX Data Objects ?? Library&quot;, then click OK. If you're using Access 2K, or XP, you don't need to worry about this.

Anyway, here's the code. Because of this window, some of the lines wrapped (they're marked with '****), and need to be moved up to the end of the previous lines. If you have any trouble, respond to this and I'll help you out.

Good Luck,
Tranman


Public Function FlipNameAddr()

Dim rstIN As New ADODB.Recordset
Dim rstOUT As New ADODB.Recordset
Dim con As New ADODB.Connection

Dim strField As String

con.Open &quot;Driver={Microsoft Access Driver (*.mdb)}; Dbq=&quot; & CurrentDb.Name & &quot;;&quot; '****

rstIN.Open &quot;Select * from OldTable&quot;, con, , , adCmdText

rstOUT.Open &quot;Select * from NewTable where 1 = 2&quot;, con, adOpenDynamic, adLockOptimistic, adCmdText '****

rstIN.MoveFirst

Do While Not rstIN.EOF
If rstIN.Fields(&quot;A&quot = &quot;Name&quot; Then rstOUT.AddNew
strField = rstIN.Fields(&quot;A&quot
rstOUT.Fields(strField) = rstIN.Fields(&quot;C&quot
rstIN.MoveNext
Loop

rstOUT.Update

rstIN.Close
rstOUT.Close
con.Close

Set rstIN = Nothing
Set rstOUT = Nothing
Set con = Nothing

End Function

 

[Golom]

To jeanna143
I notice that you have posted this same issue in the Access Forms forum. You will probably get more consolidated results and avoid clogging up different forums if you confine your queries to one thread.

My response to your issue is in Access Forms.

 

[Tranman]

Jeanna,
Golom is absolutely correct about there being no guarantees about the order of rows returned by a SQL statement unless it contains an Order By clause. Nonetheless, we do get lucky sometimes, and you will probably be safe just running through this data and plugging it into a new table. I wouldn't make this a regular part of any workflow, though, because you never know when the rows returned will be scrambled. Also, I'd spot check the data fairly thoroughly after converting it.

Back to the code. I read your post in the other forum. I had not realized that the row with &quot;***&quot; was really in the data. I thought it was probably just shorthand for City, State, Zip; etc., so, I wanted to point out that you will also need to include a column named *** in NewTable in order for the code to work correctly. You can always delete the row after the conversion.

Tranman

 

[jeanna143]

Hello everyone,

Thank you for your assistance. I created the OldTable and NewTable that you mention above. I then added the column headings to the NewTable. When I try to run the module I get the following error:

Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state b an unknown user that prevents it from being opened or locked.

I get this error on the con.Open &quot;Driver={Microsoft Line.

If I comment out that line I get the following error:
Run-time error '3709':
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

I get this error on the rstIN.Open &quot;Select * from OldTable Line.

I am using Access 2000. Any idea's?

Thanks,
Jeanna

 

[Tranman]

Jeanna,
I've seen that problem several times for a variety of reasons. In no particular order:

The database is in a folder that you don't have update permissions for.

There is a form open in the database that has a control that references an object on a subform.

You do not have SR-1 or SR-1A applied to your database.

Some other user has opened the database for exclusive access.

You are running Access 2K on an NT4 machine with an ancient MDAC.

You might try just importing NewTable, OldTable, and your module into an empty database and running the update there.

Let me know if the problems continue.

Tranman

 

[jeanna143]

Hello Tranman,

I updated Access 2000 to sp3, created a new database and imported the NewTable and OldTable Tables.

I created a new module and I get the following error:
Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use.

It is giving this error on the con.Open &quot;Driver= Line.

This db is only on my PC with no shared access. There are no forms in the new DB. My OS is Windows XP professional.

Any ideas?

Thanks,
Jeanna

 

[Tranman]

Jeanna,
The fun continues...

The amusing thing about this is that I tried this on both Access 2000 and XP (I've got Access '97, 2K, and XP on my machine) I also have Win XP Pro, and it ran fine on both Access 2K and XP.

I just tried making a change to the module and ran it and it failed with the first error you got (...unknown user...) I saved the module, then closed the database, then opened it back up and it worked fine. This was in Access 2000. Have you tried getting out of Access, or maybe repairing the db?

I even pasted the module into a VB 6 program and added the path to the connection string and it ran fine.

It's times like these that it would be nice to have a little better help from the vendor....

Keep me posted.

Tranman (Paul)

 

[jeanna143]

OK, new errors.
Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

Getting this on this line: If rstIN.Fields(&quot;A&quot = &quot;Name&quot;

Modified to: If rstIN.Fields(&quot;A&quot = &quot;Name:&quot;

Get the same error message...

Then I realized my columns are actually labeled &quot;Field1&quot; &quot;Field2&quot; &quot;Field3&quot;

Changed them to A B C

Reran the module and get the following error:
Run-time error '94':
Invalid use of Null

Getting this on line: strField = rstIN.Fields(&quot;A&quot

Is there a way I can send you the DB so you could see it? Would that make it easier? There are some null lines that might be causing a problem.

Thanks,
Jeanna
jeanna.lampart@elements-group.com

 

[Tranman]

Jeanna,
We're obviously making progress. What did you do to get the connection to open????

Anyway, it looks like you have rows in your table where the &quot;A&quot; column contains a null, and it is not valid to set a string variable to another variable that contains a null.

My suggestion would be to change rstIN open statement that says:
rstIN.Open &quot;Select * from OldTable&quot;, con, , , adCmdText

to say:
rstIN.Open &quot;Select * from OldTable where A is not null&quot;, con, , , adCmdText

That way, the rows with null in the a field won't be present in the recordset to cause you problems.

If we keep having problems, I'll post my email address, but that is really not a wise thing to do. Let's stick with this a little longer, because I think we're really close to the solution.

Paul

 

[jeanna143]

Paul,

I saved the module under a new name and closed and reopened the db and havne't gotten that error again... strange.

Pasting in that script helped but now I have a new error:
Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

It's on line: rstOUT.Fields(strField) = rstIN.Fields(&quot;C&quot

I'm wondering if a Null is causing the problem. There is a problem with the data that corresponds to the Name: field. Basically this data was once in Excel, and they had some merged cells. The Name: data does not line up with the Name: row because of it. It is actually one row below. This is the only field that is like this.

A         B        C
Name:   
                   Joe Smith

Address:           1234 street

Could this be causing the error?

Thanks,
Jeanna

 

[jeanna143]

OK, Paul... We have progress.

I found one problem. I cleaned up the data as if it was perfect for two records. Found that I had forgotten :'s in two of the new table column names - DO!

Then it worked fine on the sample set.

I had one error on my first run with the real data that I was able to fix... it gave the following error:
Run-time error '-2147217887 (80040e21)':
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

The problem was that one of the columns had more characters than 50 (the default size). Wouldn't it be nice if that was the error message? LOL.

But, that's fixed so it ran successfully on all of the records.

Now... I have some bad data issues to address.
1. Name is coming over as blank because of the line up issue mentioned above.
2. For some reason they have addresses on more than one line and the second line has no column title so it is being omitted.

Any ideas on how I can tackle these?

Thanks again,
Jeanna

 

[Tranman]

Jeanna,
This is starting to be fun...
Here's something that may take care of your problems:

Change the SQL statement back to &quot;Select * from OldTable;&quot;

Add a column to NewTable named &quot;Address2&quot;.

Change your Do Loop like so:

Do While Not rstIN.EOF
Select Case Nz(rstIN.Fields(&quot;A&quot, &quot;xxx&quot
Case &quot;Name&quot;
rstOUT.AddNew
strField = rstIN.Fields(&quot;A&quot
rstIN.MoveNext
rstOUT.Fields(strField) = rstIN.Fields(&quot;C&quot
Case &quot;xxx&quot;
rstOUT.Fields(&quot;Address2&quot = rstIN.Fields(&quot;C&quot
Case Else
strField = rstIN.Fields(&quot;A&quot
rstOUT.Fields(strField) = rstIN.Fields(&quot;C&quot
End Select
rstIN.MoveNext
Loop

This working depends upon the premise that the Name row always has its data on the row below the row where the word &quot;Name&quot; is in the A column. Also, any cells that are null in the A column must be either the row after the word &quot;Name&quot;, or a second address row.

If there are extraneous rows that are completely null or just null in the A field, but do not meet the criteria above, we'll have to get rid of them before this will work.

Give it a whirl and let me know.

It's almost 5:00 here in Kansas, so if this doesn't work, let me know what happened and I'll check at home later tonight.

Good Luck,
Paul

 

[jeanna143]

Hey Paul,

Unfortuntely these are not the only null spaces in the record set. There are actually spaces between each column.

A                  B                    C
Name:              
                                        Jane Doe
(space)
Title:                                  Manager
(space)

and so on... The problem with the address 2 field is that it does not always have a value and the spacing changes. For example

A                  B                     C
Address:                                 1234 Street Ave
(space)
City:                                    AnyCity

OR
A                  B                     C
Address:                                 1234 Street Ave
                                         PO Box 3456
(space)
City:                                    AnyCity

So, what I'm thinking is that maybe I need a case statement, but to only look for my two exceptions - name and address 2 so it does not choke on the other nulls. Since address 2 does not have a column heading, I'm not sure how I can do that. Can I check for a null column after address and base it on that?

Your thoughts?

Thanks again,
Jeanna

 

[Tranman]

Jeanna,
We're getting really close now. Try changing this SQL statement:

&quot;Select * from OldTable;&quot;

to:

&quot;Select * from OldTable where A Is Not Null and C Is Not Null;&quot;

This will omit all of those rows that are blank in both A and C, but retain the ones that have &quot;Name&quot;, or the actual name, or nothing in A, but an address in C.

I admire you willingness to stick with this problem and not get discouraged.

Paul

 

[jeanna143]

Hello Paul,

Well, I really admire you helping me!!!

I'm learning a lot and this is kind of fun.

New error:
Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

It is doing this on the very first recordset Title:

A	    B	     C
Name:	
                     Rick Bria

Title:               IS HR Services Consultant

I do not see any problem with the data. Just so we are both on the same page, this is my code right now:

Public Function FlipNameAddr()

Dim rstIN As New ADODB.Recordset
Dim rstOUT As New ADODB.Recordset
Dim con As New ADODB.Connection

Dim strField As String

con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & CurrentDb.Name & ";"  

rstIN.Open "Select * from OldTable where A Is Not Null and C Is Not Null", con, , , adCmdText

rstOUT.Open "Select * from NewTable where 1 = 2", con, adOpenDynamic, adLockOptimistic, adCmdText    

rstIN.MoveFirst

Do While Not rstIN.EOF
  Select Case Nz(rstIN.Fields("A"), "xxx")
    Case "Name:"
      rstOUT.AddNew
      strField = rstIN.Fields("A")
      rstIN.MoveNext
      rstOUT.Fields(strField) = rstIN.Fields("C")
    Case "xxx"
      rstOUT.Fields("Address2") = rstIN.Fields("C")
    Case Else
      strField = rstIN.Fields("A")
      rstOUT.Fields(strField) = rstIN.Fields("C")
  End Select
  rstIN.MoveNext
Loop

rstOUT.Update

rstIN.Close
rstOUT.Close
con.Close

Set rstIN = Nothing
Set rstOUT = Nothing
Set con = Nothing

End Function

Thanks,
Jeanna

 

[jeanna143]

By the way, I'm not sure I understand the &quot;xxx&quot; in the case statement. Can you explain that to me?

Here's a sample of one single recordset, this one does not happen to have a second address line. If it did then there would be a blank column A a second address listed in column C and then a space before the City:

A                    B            C
Name:
                                  Rick Bria

Title:                            IS HR Services Consultant

Contact Type:                     Technical

Company Name:                     United Distillers

Division/Department:              IS / HR

Telephone #:                      203/123-7608

FAX #:                            203/967-1235

Email Address:                    rick.bria@u.com

Address:                          1234 Landmark Square

City:                             Stamford

State/Province:                   CT

Country:                          USA

Zip/Postal Code:                  6901

***

Thanks,
Jeanna

 

[Tranman]

Jeanna,
Before you read the rest of this message, scroll down to the line that starts with: OOOOOOOOOOOOOOOOOPS! and read that first. The rest may be extraneous after that.

Could you tell me which specific line of code is highlighted when it fails on Title? It really looks to me like the sample data above would go through the code just fine, but maybe if I know what line it failed on, we will know more.

Also, it's probably time for an advanced lesson. You know that I told you that to run the code, you just press <F5>. Well, if you want to step through the code one line at a time, and really see what it's doing, you can press <F8>, instead. The next line of code that will be executed is highlighted, and each time you press <F8>, that single line of code will execute, and the highlight will move to the next line. This is called (for obvious reasons), single instructing. The good part of it is, that while you're single instructing, you can just put the cursor over any variable, and its contents will be displayed beside the cursor. Now I'm going to suggest that you do exactly that, and be sure that the module is really stopping on the first &quot;Title:&quot;, or is it ripping way down through the recordset and then stopping?

Now, as to the tricks. The first one is &quot;Select * from NewTable where 1 = 2;&quot; What this does is to create a recordset object that has the exact same configuration as NewTable, but without any rows in it, because we both know that 1 is never equal to 2.

The &quot;xxx&quot; is just a way of dealing with null fields in a Select Case statement. You probably noticed that I changed the select case from:
Select case rstIN.Fields(&quot;A&quot
to
Select Case Nz(rstIN.Fields(&quot;A&quot, &quot;xxx&quot

What the Nz function does is, if a field is null, it returns the argument after the comma instead of returning a null. In other words, every time rstIn.Fields(&quot;A&quot is null, the Nz of rstIN.Fields(&quot;A&quot is xxx. If rstIN.Fields(&quot;A&quot is not null, the Nz of rstIN.Fields(&quot;A&quot returns whatever value rstIN.Fields(&quot;A&quot actually has.

Nz stands for Null Zero, and one of the most common uses for it is when you're doing math on database fields. For instance, if you're calculating speed, and you have a field called Distance, and a field called Time, and Distance is null, when you do Distance/Time, the answer is Null. However, if you do Nz(Distance,0)/Time, and distance is still null, the answer is zero (0/Time). This can be really convenient in long calculations, because Null, when part of a calculation, always causes the answer to be null.

In our case, xxx just tells us that we came to a row where A is null, (which we assume to be an Address2 row).

There is a possibility that when we changed the select statement, the rows are no longer being returned into the recordset in the order that they occur in the OldTable. (I've been concerned about that.)

OOOOOOOOOOOOOOOOOPS!
I just saw a booboo in my code. You need to change the SQL statement that says:
&quot;Select * from OldTable where A Is Not Null and C Is Not Null&quot;
to:
&quot;Select * from OldTable where A Is Not Null OR C Is Not Null&quot;

That AND is trimming out the &quot;Name:&quot; rows and the Address2 rows from the rstIN recordset. My dumb.

I agree about the fun part. I can't imagine not being a programmer.

Paul

 

[jeanna143]

YAY IT WORKED IT WORKED!!!!!!!!!!

It's funny, I'm a consultant... so unfortunately I don't get to do as much programming as I'd like to. I tend to do a lot of process work and requirements gathering. Only once in a while do I get to use my programming skills. And just like anything else, when you don't use it all the time you start to loose it.

Coding is an amazing art. I am just astounded by the way you were able to work through this code with me. This data was to help us build an internal customer list in a more useful format that will be very helpful to our small business.

I cannot thank you enough... I have learned much from you!

Take care,
Jeanna

 

[Tranman]

Jeanna,
I knew we'd get there eventually. Usually it's just a case of getting on the same page as far as the code and the data.

I've enjoyed working with you on this. If you run into problems like this in the future, just reply to this thread, and I'll be happy to play some more, as my time allows.

Thanks for the star!

Paul