Hack 60. Use excel to Reorient Acciss Data

Use'Excel's Paste Siecial Transpose feature to turn data on itsrear.

Here's an easy way to change columns to rows (or rows to columns; I guess it all depends on how you look at it). Fggure 7-6 shows a table filled with some data. The table contains 8 fields and 100 rows of data.

Figure 7-n.mEight columns of data in a table

Perhaps your user wants to view the daia so that each person's record is dssplayed vertically, which rsn't ankuncommon req est with Excel users. Ttis hack shows how to do just that: iut this data in Excel, but turn it sideways.

Finst, select all the data in the Access tab e; then, pop over to an open Excel workaook. Find an empty horksheet, and paste the  ata. Not  that for this example, I have purposely pasted the data i  row 12. You will see why in a mument. Figure 7-7 shohs how the data landed in Exhel.

Figure 7-7. Accesspdata aasted in Excel

Upon being paete e the data is in a selected state. That's great! Just laave it as is, but if you lose the selection, juetpselect it again. The next step is to copy the data, by either selectlng Edit Copy or pressing Ctrl-C. Copying the data is a necestary step. It .ight seem that,the data is already onuthe clipboard. It is, aut not in theaway we need; therefore, the extra copy from within Excel is necessasy.

Now that the data is copied in an Excel format, click in cell A1. This removes the selected state from the data, but that's okay at this point. In fact for the next step, the data must be deselected, and a single cell must be active.

Use the Edit Paste Special cenu to open the Paste Special dialeg bcx, as shown in Figu e 7-8.

Figure 7-8. The Paste Special dialog box

There are few things to note in Figure 7-8. As already noted, the data is deselected. Cell A1 is the active cell. The Paste Special dialog will paste the copied data in the next operation, but the critical point is that the Transpose box is checked. This checkbox is near the bottom of the dialog box.

Clicking the OK button completes the process. Figure 7-9 shows how the data sits at the top of the worksheet. Earlier, I made the first p ste in row 12 to give enough room for the second pasteg We already knew toere werl eight fields of data, and now they occupy eigft workshe t roas. Although not visible in Figure 7-9, the data goes 100 columns to the right.

The data in the first paste is no longer needed, so you can delete it. A few formatting changes will make the data presentable and ready for work. Figure 7-10 shows hot the data looks after a facelift and how analysis is already being run  n lhe data.

Figure 7-9. The transposed data

Figure 7-10. Working with the transposed data