Returning Denormalized Output - Similar to Crosstab...but not quite

[DD]

I have a normalized table like below. This isn't my request, and I
know it doesn't make a lot of sense...but customer is always right??

ID Question Answer
1 Q1 Yes
1 Q2 Maybe
2 Q1 Yes
2 Q1 Some
2 Q1 Always
2 Q2 No
3 Q2 Why

I want the output to be 1 record per ID, but also just 1 answer per
question.

ID Q1 Q1 Q1 Q2 Q2
1 Yes Maybe
2 Yes Some Always No
3 Why

As you know pivot tables return calculated values in middle and would
group the "Q1".
I'm not a VB coder but any suggestions?? Again, I know it's poor
output. We tried to tell them to use the normal table but I don't
think they are that good with analysis.

 

[Jeff Boyce]

The customer is always ... the customer. Helping the customer do something
you know is wrong or fraught with danger is ... "enabling", and I don't mean
in a positive way.

But if you have a customer/contractor relationship based on "do what I want,
don't think", yes, the customer is right.

I'm not following how your initial table is normalized. It seems to me that
you have multiple questions with the same ID, and multiple IDs with the same
question/answer. In my mind, a normalized table would have one ID with each
valid Q/A pair.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[DD]

There are sometimes multiple answers per question, which is why it
looks the way it does now. Even if it's not a fully normalized
table...which I'd agree it is not as we'd really want a couple tables
for that, given that the table is as described below, are there
suggestions for getting into the alternate output? I've tried Access
and Excel...am pretty good at both, but can't figure out a way to do
as described below. Customer in this case is the government and this
is the final deliverable. So fine...it's not how I was taught for
output tables, but if that's what you want...here!

 

[Jerry Whittle]

Check out Crosstab queries in Help.

 

[KARL DEWEY]

You could use a crosstab query but your data is not logical.
How do you get 3 different answers to Q1 for ID 2?

 

[Gina Whipp]

DD,

Not sure why you think this can't be done in a Crosstab query. Have you
tried? Did it not give you the results you want? If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that. You give them what they want to *see* but do it
right in the background. The customer should NEVER see the tables so having
them normalized should not interfere with the display the way the Client
wants. Doesn't help you now, might actually be hurting because unnormalized
tables always present a challenge when trying to extract information. Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[DD]

DD,

Not sure why you think this can't be done in a Crosstab query.  Have you
tried?  Did it not give you the results you want?  If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that.  You give them what they want to *see* but doit
right in the background.  The customer should NEVER see the tables so having
them normalized should not interfere with the display the way the Client
wants.  Doesn't help you now, might actually be hurting because unnormalized
tables always present a challenge when trying to extract information.  Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The crosstabs output, using above is either the below
or same but on a horizontal axis:

ID Q1
1 Yes 1
2 Yes 1
2 Some 1
2 Always 1

 

[malick]

malick tine

 

[DD]

DD,

Not sure why you think this can't be done in a Crosstab query.  Have you
tried?  Did it not give you the results you want?  If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that.  You give them what they want to *see* but doit
right in the background.  The customer should NEVER see the tables so having
them normalized should not interfere with the display the way the Client
wants.  Doesn't help you now, might actually be hurting because unnormalized
tables always present a challenge when trying to extract information.  Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The closest I can come using Crosstab is below, but I
still have to use "First" or "Last" to get any text values in data. I
want all of them.

TRANSFORM First([Lyme Disease Comma Export 20091029].Response) AS
FirstOfResponse
SELECT [Lyme Disease Comma Export 20091029].Surveyid, First([Lyme
Disease Comma Export 20091029].Response) AS [Total Of Response]
FROM [Lyme Disease Comma Export 20091029]
GROUP BY [Lyme Disease Comma Export 20091029].Surveyid
PIVOT [Lyme Disease Comma Export 20091029].Question;

 

[Dale Fye]

DD,

If you don't mind your output looking like:

ID Q1-1 Q1-2 Q1-3 Q2-1 Q2-2
1 Yes Maybe
2 Yes Some Always No
3 Why

then you could write your data to a temp table and then compute a sequence
number for each ID/question combination; like:

ID Question Answer Seq#
1 Q1 Yes 1
1 Q2 Maybe 1
2 Q1 Yes 1
2 Q1 Some 2
2 Q1 Always 3
2 Q2 No 1
3 Q2 Why 1

Then concatenate the Question # and the Sequence number in the column header
of the cross-tab query.

Or, you could concatenate the Q# and the Answer into the column headings,
and just put an X in the matrix where the ID, and Q#/Answer correspond.

 

[DD]

DD,

Not sure why you think this can't be done in a Crosstab query.  Have you
tried?  Did it not give you the results you want?  If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that.  You give them what they want to *see* but doit
right in the background.  The customer should NEVER see the tables so having
them normalized should not interfere with the display the way the Client
wants.  Doesn't help you now, might actually be hurting because unnormalized
tables always present a challenge when trying to extract information.  Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

The table really is normalized though. We aren't doing the analysis,
the client wants to do it's own analysis so wants the tables. The
table is actually like below. The "ID' is actually the survey number.

ID Survey # Question Answer
1 1 q1 yes
2 1 q1 no
3 2 q1 yes

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The closest I can come using Crosstab is below, but I
still have to use "First" or "Last" to get any text values in data. I
want all of them.

TRANSFORM First([Lyme Disease Comma Export 20091029].Response) AS
FirstOfResponse
SELECT [Lyme Disease Comma Export 20091029].Surveyid, First([Lyme
Disease Comma Export 20091029].Response) AS [Total Of Response]
FROM [Lyme Disease Comma Export 20091029]
GROUP BY [Lyme Disease Comma Export 20091029].Surveyid
PIVOT [Lyme Disease Comma Export 20091029].Question;

 

[Gina Whipp]

Wasn't thinking... that is valid point...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

DD,

Not sure why you think this can't be done in a Crosstab query. Have you
tried? Did it not give you the results you want? If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that. You give them what they want to *see* but do it
right in the background. The customer should NEVER see the tables so
having
them normalized should not interfere with the display the way the Client
wants. Doesn't help you now, might actually be hurting because
unnormalized
tables always present a challenge when trying to extract information. Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The closest I can come using Crosstab is below, but I
still have to use "First" or "Last" to get any text values in data. I
want all of them.

TRANSFORM First([Lyme Disease Comma Export 20091029].Response) AS
FirstOfResponse
SELECT [Lyme Disease Comma Export 20091029].Surveyid, First([Lyme
Disease Comma Export 20091029].Response) AS [Total Of Response]
FROM [Lyme Disease Comma Export 20091029]
GROUP BY [Lyme Disease Comma Export 20091029].Surveyid
PIVOT [Lyme Disease Comma Export 20091029].Question;

 

[Gina Whipp]

DD,

Yes, I realized using First or Last does not produce ALL the results.
(Also, seeing that the table IS actually normalized is a sigh of relief,
though it does not help your issue.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

DD,

Not sure why you think this can't be done in a Crosstab query. Have you
tried? Did it not give you the results you want? If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that. You give them what they want to *see* but do it
right in the background. The customer should NEVER see the tables so
having
them normalized should not interfere with the display the way the Client
wants. Doesn't help you now, might actually be hurting because
unnormalized
tables always present a challenge when trying to extract information. Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

The table really is normalized though. We aren't doing the analysis,
the client wants to do it's own analysis so wants the tables. The
table is actually like below. The "ID' is actually the survey number.

ID Survey # Question Answer
1 1 q1 yes
2 1 q1 no
3 2 q1 yes

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The closest I can come using Crosstab is below, but I
still have to use "First" or "Last" to get any text values in data. I
want all of them.

TRANSFORM First([Lyme Disease Comma Export 20091029].Response) AS
FirstOfResponse
SELECT [Lyme Disease Comma Export 20091029].Surveyid, First([Lyme
Disease Comma Export 20091029].Response) AS [Total Of Response]
FROM [Lyme Disease Comma Export 20091029]
GROUP BY [Lyme Disease Comma Export 20091029].Surveyid
PIVOT [Lyme Disease Comma Export 20091029].Question;

 

[Gina Whipp]

Question... How could Q1, Q2, etc... have two answers?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

DD,

Not sure why you think this can't be done in a Crosstab query. Have you
tried? Did it not give you the results you want? If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that. You give them what they want to *see* but do it
right in the background. The customer should NEVER see the tables so
having
them normalized should not interfere with the display the way the Client
wants. Doesn't help you now, might actually be hurting because
unnormalized
tables always present a challenge when trying to extract information. Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

The table really is normalized though. We aren't doing the analysis,
the client wants to do it's own analysis so wants the tables. The
table is actually like below. The "ID' is actually the survey number.

ID Survey # Question Answer
1 1 q1 yes
2 1 q1 no
3 2 q1 yes

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The closest I can come using Crosstab is below, but I
still have to use "First" or "Last" to get any text values in data. I
want all of them.

TRANSFORM First([Lyme Disease Comma Export 20091029].Response) AS
FirstOfResponse
SELECT [Lyme Disease Comma Export 20091029].Surveyid, First([Lyme
Disease Comma Export 20091029].Response) AS [Total Of Response]
FROM [Lyme Disease Comma Export 20091029]
GROUP BY [Lyme Disease Comma Export 20091029].Surveyid
PIVOT [Lyme Disease Comma Export 20091029].Question;

 

[Gina Whipp]

DD,

This is the closet that I could come but it doesn't seperate Questions per
Answer

TRANSFORM Last(YourTable.Answer) AS LastOfAnswer
SELECT YourTable.ID, YourTable.SurveryID
FROM YourTable
GROUP BY YourTable.ID, YourTable.SurveryID
PIVOT YourTable.Question;

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

DD,

Not sure why you think this can't be done in a Crosstab query. Have you
tried? Did it not give you the results you want? If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that. You give them what they want to *see* but do it
right in the background. The customer should NEVER see the tables so
having
them normalized should not interfere with the display the way the Client
wants. Doesn't help you now, might actually be hurting because
unnormalized
tables always present a challenge when trying to extract information. Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

The table really is normalized though. We aren't doing the analysis,
the client wants to do it's own analysis so wants the tables. The
table is actually like below. The "ID' is actually the survey number.

ID Survey # Question Answer
1 1 q1 yes
2 1 q1 no
3 2 q1 yes

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The closest I can come using Crosstab is below, but I
still have to use "First" or "Last" to get any text values in data. I
want all of them.

TRANSFORM First([Lyme Disease Comma Export 20091029].Response) AS
FirstOfResponse
SELECT [Lyme Disease Comma Export 20091029].Surveyid, First([Lyme
Disease Comma Export 20091029].Response) AS [Total Of Response]
FROM [Lyme Disease Comma Export 20091029]
GROUP BY [Lyme Disease Comma Export 20091029].Surveyid
PIVOT [Lyme Disease Comma Export 20091029].Question;

 

[DD]

DD,

If you don't mind your output looking like:

ID    Q1-1    Q1-2     Q1-3    Q2-1   Q2-2
1      Yes                            Maybe
2      Yes     Some    Always   No
3                                       Why

then you could write your data to a temp table and then compute a sequence
number for each ID/question combination; like:


Then concatenate the Question # and the Sequence number in the column header
of the cross-tab query.  

Or, you could concatenate the Q# and the Answer into the column headings,
and just put an X in the matrix where the ID, and Q#/Answer correspond.

Dale's suggestion is very ugly...but I like it and seems to give the
output they want! I just did this, had to export to Excel to make the
sequencing easier then import back to Access. Only issue I have now
is too many column headers in Crosstab (459). There are 79 questions
and, now using each of the different answers as 1 possibility = 459.
I'm not sure if Excel has the same limitation so I may try that. I'd
have same issue if I reversed the axes because there are 620 survey
IDs.

Anyone know how to get around the # of column headers?

 

[Gina Whipp]

DD,

That is set in stone... You will probably have to do 2 to 3 queries to get
around that!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

DD,

If you don't mind your output looking like:

ID Q1-1 Q1-2 Q1-3 Q2-1 Q2-2
1 Yes Maybe
2 Yes Some Always No
3 Why

then you could write your data to a temp table and then compute a sequence
number for each ID/question combination; like:


Then concatenate the Question # and the Sequence number in the column
header
of the cross-tab query.

Or, you could concatenate the Q# and the Answer into the column headings,
and just put an X in the matrix where the ID, and Q#/Answer correspond.

Dale's suggestion is very ugly...but I like it and seems to give the
output they want! I just did this, had to export to Excel to make the
sequencing easier then import back to Access. Only issue I have now
is too many column headers in Crosstab (459). There are 79 questions
and, now using each of the different answers as 1 possibility = 459.
I'm not sure if Excel has the same limitation so I may try that. I'd
have same issue if I reversed the axes because there are 620 survey
IDs.

Anyone know how to get around the # of column headers?

 

[DD]

DD,

If you don't mind your output looking like:

ID    Q1-1    Q1-2     Q1-3    Q2-1   Q2-2
1      Yes                            Maybe
2      Yes     Some    Always   No
3                                       Why

then you could write your data to a temp table and then compute a sequence
number for each ID/question combination; like:


Then concatenate the Question # and the Sequence number in the column header
of the cross-tab query.  

Or, you could concatenate the Q# and the Answer into the column headings,
and just put an X in the matrix where the ID, and Q#/Answer correspond.

Dale's suggestion is very ugly...but I like it and seems to give the
output they want! I just did this, had to export to Excel to make the
sequencing easier then import back to Access. Only issue I have now
is too many column headers in Crosstab (459). There are 79 questions
and, now using each of the different answers as 1 possibility = 459.
I'm not sure if Excel has the same limitation so I may try that. I'd
have same issue if I reversed the axes because there are 620 survey
IDs.

NEVER MIND ABOVE - I got it. I did 10 seperate crosstab queries and
exported those to a csv. Then imported those 10 back as new tables
and then combined the 10 tables into 1! What a mess! I'll tell my
client...good luck with your analysis. Oh, and whatever conclusion
you reach, it either took too long to get there or it's
wrong...because this table is a mess!

 

[Dale Fye]

My guess is that the client wants to use some analytical softwared (SPSS,
Answer Tree) or something like that to do the analysis of this data. Very
powerful tool, but it requires (or at least the version that I used) that
the data be in a format like what is described by DD.

Maybe one of these days, they will figure out a way to accept well
normalized survey data and automatically format it for their needs.

Dale

DD,

Not sure why you think this can't be done in a Crosstab query. Have you
tried? Did it not give you the results you want? If you did and it didn't,
show us the SQL of the query.

As a side note, yeppers Client is always right except when they are wrong
but they don't know that. You give them what they want to *see* but do it
right in the background. The customer should NEVER see the tables so
having
them normalized should not interfere with the display the way the Client
wants. Doesn't help you now, might actually be hurting because
unnormalized
tables always present a challenge when trying to extract information. Just
some food for thought for your next project!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Crosstab doesn't work though because the data area for crosstabs is
always a calculated value. I want textual data, not a sum...count,
etc. I'm familiar with crosstabs but it's not quite right in this
case. If you didn't have to use a calculated value for the data
portion then we'd be closer. But still, the crosstab would group the
column headings such as "Q1". If 1 respondent provided 3 different
answers to Q1, like ID 2 above, then I'd want the output to look just
like the above. The crosstabs output, using above is either the below
or same but on a horizontal axis:

ID Q1
1 Yes 1
2 Yes 1
2 Some 1
2 Always 1

 

[Gina Whipp]

Now THAT would be nice... Then DD would not have to jump thru hoops
surrounded by fire!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm