I am a newbie and am moving my Excel DB into ACCESS as it has become unwieldy.
It is a Survey DB to analysis 10,000 surveys that ask 23 questions (23 fields).
I built a master table and a number of other relational tables. I believe I have a much normalized db right now. I am at the stage of building queries to analyze the data and am running into trouble. I need to COUNT the number of responses to each question (Count of each field) with the condition that any records with an 8 or 9 in them be excluded from the COUNT; as well as calculate AVERAGES for each question with the same criteria to remove any records with an 8 or 9 in them. (8 or 9 refers to responses of Don't Know or No Response) Each query was built using the Design grid and includes the fields of YEAR and QUARTER to group the results by year and then quarter.
To start with I built a separate query for each question to return the average for that question as well as the COUNT. I did this in order to check my new DB against my old Excel DB and analysis tool in order to verify that all my records were correct and had imported correctly. They matched perfectly and returned the correct results! This is great because it tells me I have correctly built my master table and imported my raw data.
However, I do not want 23 separate Queries that return Averages and 23 that returns COUNTS for each of my survey questions (fields). What I want is all of the averages for each question to be in one master table (Query) and all the COUNTS for each question to be in one master table (query) so that I can export these and use them for analysis. But, when I start to combines all these queries into one query using the design view to copy and paste everything from each query into the 1 new master query everything goes wrong. All the averages and counts no longer calculate correctly and I may or may not understand why but one things for sure I am stumped as to how to fix this so the master query of averages returns the same results I got when each field was split into its own separate query.
BACKGROUND INFO: As mentioned I have criteria built into all 23 queries so that any records that contain an 8 or a 9 are excluded from the counts and also excluded from the averages.
I need this data to remain in my raw data (tbl_responses) for future use but do not need it or want it in my analysis of averages and counts.
The Queries are grouped by Year and By Quarter and have a section in the design grid for each question that uses the “WHERE” functions and between 1 and 7 as the criteria to exclude numbers 1 and 8.
As mentioned I have verified that each of the 23 individual queries that I built are returning the right counts and correct averages but when I try to bring all of these into one master Query the COUNTS are not filtering the correct number of 8's and 9's out of the results and all of the averages are off by varying degrees. I cannot see the pattern that is making this occur.
I’ve tried to cut and paste the SQL code from each Query into 1 new master query and then use the UNION operator to combine them. It works to return the correct averages and counts however it jams the results all into 1 fields (Question 1) and excludes all the other fields (Questions 2-23) The other concern is that I don’t know if this Union Query will work to update as more surveys are uploaded to this master tbl_responses table in the future and if not this Union solution is not the right fix because I need my queries to update automatically as the DB grows.
I have also tried to use the Davg function in design grid mode to no affect and I must admit I don’t really get this function anyway. It was just something I tried.
Someone please help me. Please bear with me I am new to this.
Thanks.