Survey DB Query not correctly Calculating COUNTS and AVERAGES by year and quarter.

[kevin007]

Hi jzwp11, Hopefully we can pick up where we left off in this thread.

Im well along in restructuring as per your advice. I hope you can answer a quesiton about a problem I haveing in regards to the multiple scales that are used thropughout this survey. Can we please go back to where you last recommended the following...(I have all the other tables nearly built up to this point.)

In terms of the possible responses, we can categorize those as well. I think we can use the same question categories here also (?)

tblPossibleResponses
-pkPResponseID primary key, autonumber
-txtResponseText
-longResponseRankValue
-fkQuestionCatID

I’m trying to figure out how to deal with all the scales that are used in the various question categories. How to link up all the possible scales to the appropriate questions etc.



I have built the tblPossibleResponses you mention above but the last field, fkQuestionCatID, cannot be used as you had asked before. In the question categories listed in the Question category table that I have built there are many different scales used in each of these categories.

For example, I have the usual 1-9 for the main questions, I have yes or no for some I have yes/no no response for some. I have yes/no don’t know for some, I have a different 1-9 for the loyalty scales, I have a 1-7 for the different occupation choices they had, I have a 1-6 for the type of fare products, 1 have a 1-5 for the type of weather that day, same for temperature, etc etc. I’m a little confused about how to store the possible response scales for these categories and how they link up in my table relationships in terms of foreign and primary keys.

Thanks

Kevin


Can you please also re-explain the longResponseRankValue feild. Im lost and confused about exactly what this feild means? Which of my feilds go here. Sorry I just didn;t comprehend this part...

[jzwp11]

The longResponseRankValue field would hold the numerical value like your 1-9 etc. that you mention.

Regarding the categories, if a question can have a series of possible responses and the responses may occur in multiple category that describes a many-to-many relationship which is handled with a junction table

tblQuestionCategory
-pkQuestionCatID primary key, autonumber
-txtQuestionCategoryName


tblPossibleResponses
-pkPResponseID primary key, autonumber
-txtResponseText
-longResponseRankValue


tblQuestionsPossibleReponses
-pkQPResponsesID primary key, autonumber
-fkQuestionCatID foreign key to tblQuestionCategory
-fkPResponseID foreign key to tblPossibleResponses

[kevin007]

Id really appreciate it if you would have a look at my relationships. I tried to attach both a copy of my original db structure with the one big flat table and the new structure I built based on your advice. I will send this but it dosnt appear that they have attached. I would really appreciate it if you would verify that my table sturctures are correct according to your outline.

I would be more than happy to send them to an alternative external email adress if you dont mind. If they ahvnt been attached here.

The longResponseRankValue field would hold the numerical value like your 1-9 etc. that you mention.

Regarding the categories, if a question can have a series of possible responses and the responses may occur in multiple category that describes a many-to-many relationship which is handled with a junction table

tblQuestionCategory
-pkQuestionCatID primary key, autonumber
-txtQuestionCategoryName


tblPossibleResponses
-pkPResponseID primary key, autonumber
-txtResponseText
-longResponseRankValue


tblQuestionsPossibleReponses
-pkQPResponsesID primary key, autonumber
-fkQuestionCatID foreign key to tblQuestionCategory
-fkPResponseID foreign key to tblPossibleResponses

[kevin007]

Hi Plehed a look at the relationships. As you can see its confused. With all the repys Ive had and changes youve suggested I am lost here. As you can see Table Participants is not related to ANYTHING! I dont see how to do so. Everything you've suggested about the following....

Since you have other people involved (agent) and not just participants, we need to generalize the tblParticipants to tblPeople and will have to cascade that change to other tables

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-dteBirth (date of birth)


tblPeopleType (2 records Participant and Agent)
-pkPeopleTypeID primary key, autonumber
-txtPeopleType

....is not to be used. The confusion is that you assumed justifyably that the AGEN name was relevent. We may take this records completely out of this DB. We dont need to analyse anything with the AGENT and its not a relevent feilds.

Therefore I went back to one of the eairlier suggesitons you had to build a tbl_participants (insead of tbl_people) trying to integrate that design with all the future tasbles has been really confusing. I am going to send this away with the attached word doc screen shot of the relaitonships I have. Imediately afdter that I am going to try to attach the ACCESS file....but it dosnt seem to want to accept it here.

Please let me know what you think of these tables and their relationships so far. Obviously it isn't right yet. can you PLEASE ! tell me how to get there?

[kevin007]

I got it in. Please have a look and feel free to change whatever you feel needs changing right in the attached file and re attach it. That would be wonderfull!!!

[jzwp11]

The relationships look OK, you will have to fill in any additional fields relative to each table based on your application. Once that is complete, then you will be ready to create some forms. Then I think you had some existing data that you wanted to migrate, correct?

[kevin007]

Thanks for looking. YES I do have data to migrate. I have it all in the original tbl_repsonses_2_0 that I showed the structure of originally. But do you have any idea where I put all these fields? I would welcome some further clarificaiton.

I am sorry but I still dont understand why we have a tblParticipantSurvey and a tblSurvey and then have a tbl participantSurveyQuesitons and a tblQuesitons and then why we have both a tblPossibleResponses and a tblQuesitonsPossible Responses…this is confusing to me. I am not qualified enough in this area to comprehend the necessity of this structure and that makes it difficult for me to know where to put my data.

Based on this table structure where does my data go? Where do all my fields go? Could you provide further detail.

ALSO I can't comprehend how this structure will work when I have tbl_participants all off to its own. ITS NOT EVEN CONNECTED! How can this be right? Its a table in isolation and it has no connection to the other tbales. Im confused. Sorry but if you could provide some specific details on how the feilds I have go into this structure that would really be helpful. I can send you a list of all the fields I now use. mabey you can help me put them in the right table?

[kevin007]

The relationships look OK, you will have to fill in any additional fields relative to each table based on your application. Once that is complete, then you will be ready to create some forms. Then I think you had some existing data that you wanted to migrate, correct?

All these fields need a home in the new structuire. How do I do THAT?

[jzwp11]

BTW, doing a survey database is one of the more intricate types of databases.

You do have the tblParticipants linked to tblParticipantSurvey, so it is not off by itself with no relations.

I am sorry but I still dont understand why we have a tblParticipantSurvey and a tblSurvey and then have a tbl participantSurveyQuesitons and a tblQuesitons and then why we have both a tblPossibleResponses and a tblQuesitonsPossible Responses…this is confusing to me. I am not qualified enough in this area to comprehend the necessity of this structure and that makes it difficult for me to know where to put my data.

The table structure follows what you said throughout the discussion. You said that you have more than one survey, so you will have 2 records in tblSurvey. A participant (tblParticipant) can take either of the 2 surveys so you have to relate the participant with the survey they are taking (this happens in tblParticipantSurvey). Since a survey can have a different assemblage of questions we have to relate the questions to the survey. Now this is something that I had not discussed yet. As the structure stands right now, each time a survey needs to be done, you have to add the appropriate questions to the survey/participant based on what survey they are taking. This can take time and is prone to error (you might miss adding a question or two). What would be more efficient & avoid errors is to assign each question to each of the 2 surveys ahead of time, much like a template. And then when you are ready to conduct a particular survey (when you assign it to a participant), you can have Access automatically "grab" the appropriate list of questions and assign them to the participant's particular survey. This would be done with an append query. Basically you would set up the templates once. Of course, you can always add new questions to an existing template or create new templates as your needs change. Assuming that you would want this capability, I have added an additional table tblSurveyQuestions to the structure. The revised DB is attached.


I will have to look at your old design more closely tonight to be able to tell you where things go.