Good Looking Bloke
Registered User.
- Local time
- Today, 19:49
- 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:
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...
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.
Which gives my expected wording and the correct data. However when I cross tab this query I am getting some weird results...
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.
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:
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...
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...
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.