Re: Second Data Reshaper for Microsoft Excel

Cool! I am always amazed at the things possible in excel. However, I often point out how easy data based things can be in a database. For example If you had the same data in Microsoft Access you could just do a self join on the table and return the same results.

I created a small three column table named tbl_input with columns to match your input columns (ID,Truck,Car,Bike) I input data to match. Then made the following sql query to return the same results

(SELECT tbl_input.ID, "Truck" AS Vehicle, tbl_input_1.Truck
FROM tbl_input AS tbl_input_1 INNER JOIN tbl_input ON tbl_input_1.ID = tbl_input.ID)
UNION ALL
(
SELECT tbl_input.ID, "Car" AS Vehicle, tbl_input_1.Car
FROM tbl_input AS tbl_input_1 INNER JOIN tbl_input ON tbl_input_1.ID = tbl_input.ID)
UNION ALL
(
SELECT tbl_input.ID, "Bike" AS Vehicle, tbl_input_1.Bike
FROM tbl_input AS tbl_input_1 INNER JOIN tbl_input ON tbl_input_1.ID = tbl_input.ID);

ID Vehicle Truck
1234 Truck $4,700
1235 Truck $32,000
1236 Truck N/A
1234 Car
1235 Car $12,000
1236 Car Don’t Know
1234 Bike
1235 Bike N/A
1236 Bike $300

I had more time in inputing the data than writing the query

mike J.

Re: Second Data Reshaper for Microsoft Excel

Mike,
Yes it was much easier to do this in MS Access. However, a lot --more than I would care to quote-- of people I work with are intimidated by Database applications MS Access included. Both Brad and I are MS Access junkies, but we realize that not everyone is. Also some people don’t like switching back and forth between applications; they’d prefer to do it all in one application and most calculations verses data query are best in EXCEL. I’ve several dozen MS EXCEL I’ve posted on the Microsoft Office templates online site and about ½ a dozen MS Access applications that will be posted soon, just prior to Office 2010 (Agreement I have with the MS Access Dev Team).

Re: Second Data Reshaper for Microsoft Excel

One thing I forgot to mention: Your example is explicit. While simple for a three-column dataset, it becomes problematic for my dataset, which has well over 500 columns of data for each respondent.

Re: Second Data Reshaper for Microsoft Excel

You are right. I understand that there are many people that feel more comfortable with excel and your application may be completely justified. When you get large amounts of data it can become unmanageable in a spreadsheet. I just like to point out the flexibility of data in a database. People often see a database as this rigid thing.

To give a more generic example. If you had structured the data differently on input which would probably been a better choice anyway. You could have structure it like

UserID
Question
Answer

Then the base data would look something like what you reshaped to

surveyQs
ID User question answer
1 1234 truck $4,700
2 1235 truck $32,000
3 1236 truck N/a
4 1234 car
5 1235 car $12,000
6 1236 car Don't Know
7 1234 bike
8 1235 bike N/A
9 1236 bike $300

Then you could do a crosstab query to get to the other format you were using

TRANSFORM First(surveyqs.answer) AS FirstOfanswer
SELECT surveyqs.id
FROM surveyqs
GROUP BY surveyqs.id
PIVOT surveyqs.qtype;

User bike car truck
1234 $4,700
1235 N/A $12,000 $32,000
1236 $300 Don't Know N/a

Once you get the data in a database then all sorts of things are possible. One of those things would be to query it from Excel.

"To the man who only has Excel, Everything looks like a spreadsheet"

Re: Second Data Reshaper for Microsoft Excel

You are right. I understand that there are many people that feel more comfortable with excel and your application may be completely justified. When you get large amounts of data it can become unmanageable in a spreadsheet. I just like to point out the flexibility of data in a database. People often see a database as this rigid thing.

To give a more generic example. If you had structured the data differently on input which would probably been a better choice anyway. You could have structure it like

UserID
Question
Answer

Then the base data would look something like what you reshaped to

surveyQs
ID User question answer
1 1234 truck $4,700
2 1235 truck $32,000
3 1236 truck N/a
4 1234 car
5 1235 car $12,000
6 1236 car Don't Know
7 1234 bike
8 1235 bike N/A
9 1236 bike $300

Then you could do a crosstab query to get to the other format you were using

TRANSFORM First(surveyqs.answer) AS FirstOfanswer
SELECT surveyqs.id
FROM surveyqs
GROUP BY surveyqs.id
PIVOT surveyqs.qtype;

User bike car truck
1234 $4,700
1235 N/A $12,000 $32,000
1236 $300 Don't Know N/a

Once you get the data in a database then all sorts of things are possible. One of those things would be to query it from Excel.

"To the man who only has Excel, Everything looks like a spreadsheet"

Re: Second Data Reshaper for Microsoft Excel

Thanks for giving good information.I really like it.Thanks to you.mcse practice exams