Troubling Cross Tab Query (1 Viewer)

Good Looking Bloke

Registered User.
Local time
Today, 17:57
Joined
Oct 15, 2019
Messages
30
This is proving elusive at best...hair removing is probably a better description.

I am mucking with a 'survey' I don't actually think it is, but its what its called, so lets stay with that description for the discussion.

In Excel the survey data is presented:
Survey.jpg

There are other complications but lets deal with this one.

I am wanting to build a similar layout in Access which should be a cross tab query. However it is eluding me how to do this.

Attached is my playing with ideas around the data structure and query. However in summary the issue is...if I run a cross tab query on my table of responses I get the kind of correct information...
KindaCorrect.jpg

However the column headings are the group and question identification numbers and not the actual text.

I then thought okay that works fine, except the words, so I will create a query explicitly with the wording included.

Code:
SELECT tblSurveyResponse.PersonID, tblSurveyResponse.CounsellorID, tblSurveyResponse.SurveyDate, [tlkupSurveyGroup].[Group] & " " & [tblSurveyQuestion].[Question] AS CTHead, tblSurveyResponse.Response
FROM tlkupSurveyGroup LEFT JOIN (tblSurveyQuestion LEFT JOIN tblSurveyResponse ON tblSurveyQuestion.QuestionID = tblSurveyResponse.Question) ON tlkupSurveyGroup.GroupID = tblSurveyQuestion.Group;

Which gives my expected wording and the correct data. However when I cross tab this query I am getting some weird results...
WeirdResult.jpg

I have hidden some of this to try and make the issue clearer...However A stop gap should have a sister question being 2 with the answer Don't really know and this is completely missing.

I have tried playing with the joins of course, but something is eluding me here. I don't play with cross tab enough to get what this is. I have attached my play so someone else can have a look and laugh at the old fella missing something so obvious...and hopefully tell me what it is.
 

Attachments

  • SurveyOnOwn.accdb
    1.2 MB · Views: 130

plog

Banishment Pending
Local time
Today, 04:57
Joined
May 11, 2011
Messages
11,611
You get 1 field to use as columnheaders in a crosstab. So, exactly replicating your Excel report in Access is a no go. What you could do in Access is merge your 2 headers into 1 where the Question prefixes each subheading:

Q1-How would....Financial Situation | Q1-How would...Other Text | Q2 How has...a) Decrease...| Q2 How has...b)...

Or, you could leave your report in Excel and keep it exactly as it is. You would create a non-crosstab query to generate the data needed. Run it and paste your data in Excel, on another tab use a pivot table based on that data and you would have the report look like it does now. Then to update it you would copy over the data and refresh the pivot.

The third option and the one I recommend--a new layout. MAke it a vertical report more native to Access. I am sure you can get it to look just as good, just not similiar to what you are used to now.
 

Good Looking Bloke

Registered User.
Local time
Today, 17:57
Joined
Oct 15, 2019
Messages
30
You get 1 field to use as columnheaders in a crosstab. So, exactly replicating your Excel report in Access is a no go. What you could do in Access is merge your 2 headers into 1 where the Question prefixes each subheading:

Q1-How would....Financial Situation | Q1-How would...Other Text | Q2 How has...a) Decrease...| Q2 How has...b)...

Or, you could leave your report in Excel and keep it exactly as it is. You would create a non-crosstab query to generate the data needed. Run it and paste your data in Excel, on another tab use a pivot table based on that data and you would have the report look like it does now. Then to update it you would copy over the data and refresh the pivot.

The third option and the one I recommend--a new layout. MAke it a vertical report more native to Access. I am sure you can get it to look just as good, just not similiar to what you are used to now.

Thanks for the suggestion. I have already attempted to merge the questions to generate the headers as in the SQL statement [tlkupSurveyGroup].[Group] & " " & [tblSurveyQuestion].[Question] AS CTHead, however that is what is causing the odd outcomes. I know I cant replicate the excel sheet I am not trying too. I am wanting to acheive something like:

Q1-How would....Financial Situation | Q1-How would...Other Text | Q2 How has...a) Decrease...| Q2 How has
Stop Gap Don't really know False true

But I cant so far, see the attached db to see what I mean.
 

June7

AWF VIP
Local time
Today, 01:57
Joined
Mar 9, 2014
Messages
5,423
Apparently the merged data is too long for a field name.

Also recommend a more conventional report design.
 

Good Looking Bloke

Registered User.
Local time
Today, 17:57
Joined
Oct 15, 2019
Messages
30
Apparently the merged data is too long for a field name.

Also recommend a more conventional report design.

I would love too...lets all write to the federal government and ask them to change their report...If fields are too long I guess I will have to ask the user to manually transcribe :(
 

June7

AWF VIP
Local time
Today, 01:57
Joined
Mar 9, 2014
Messages
5,423
Well, there are ways to emulate government report which is a non-normalized arrangement of data. If CROSSTAB can't handle then maybe VBA and temp table. I have done this.
 

Good Looking Bloke

Registered User.
Local time
Today, 17:57
Joined
Oct 15, 2019
Messages
30
Well, there are ways to emulate government report which is a non-normalized arrangement of data. If CROSSTAB can't handle then maybe VBA and temp table. I have done this.

Thanks I think this the way to go. Bit more work but still thats the way it is. Was going to have issues later pushing this out to merge with other data anyway. Thanks
 

Users who are viewing this thread

Top Bottom