Odd query to turn a table generated by Joomla into useful data

**dipique** · 10-17-2012, 06:10 AM

I manage a web site and users fill out a form on the web site. The site is built on Joomla, and there's a little module that handles the form for me. It puts the data in a weird format though, like this:

Form
Name:
Phone Number:
Comment:

Data

ID	Record	Question	Type	Value
1	1	Name	Text	Bill Johnson
2	1	Phone Number	Number	1234567890
3	1	Comment	Text	This is awesome!
4	2	Name	Text	Mark Myers
5	2	Phone Number	Number	7546551231
6	2	Comment	Text	This is my comment.
7	3	Name	Text	Phil Connor
8	3	Phone Number	Number	38217483719
9	3	Comment	Text	I hrt test data!

I would like to use a query to turn this data into a standard format, like this:

Record	Name	Phone Number	Comment
1	Bill Johnson	1234567890	This is awesome!
2	Mark Myers	7546551231	This is my comment.
3	Phil Connor	38217483719	I hrt test data!

This goes beyond my knowledge of Access or SQL. Any ideas? An ideal solution wouldn't use any access-specific query elements, but I can live with non-ideal.

Thanks!

Dan

**JoeM** · 10-17-2012, 06:27 AM

One way to do this is to create three queries:
1. Just return the records where "Question = Name"
2. Just return the records where "Question = Phone Number"
3. Just return the records where "Question = Comment"

Then, create a new query, joining these three queries on your Record field, and return the appropriate field from each query, and you should have what you want!