Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21

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

    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.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum.

    When you say that you have 23 fields (one for each question), it tells me that your database is not structured (i.e. not normalized) correctly and anything you do with this structure such as creating queries will cause you problems. What would happen if you had to add an additional question? You would have to redesign your table and all associated queries, forms and reports!

    The questions should be records in a table not fields in a table.

    With that said, you need to rework your table structure, but to do the structure correctly we need to understand more about your surveys. So, here are some questions that come to mind.

    Do you present the survey taker with a choice of possible answers or are the answers free form text?

    Who takes your surveys & do you track the people who take them?

    Can there be multiple responses to a question?

    Is every respondent presented with the same questions for each survey?

    Do you have multiple types of surveys each with their own assemblage of questions?

    Does the same person take the survey multiple times?

  3. #3
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    Thanks for your reply. You are right the table is not completely normalized. I do hope that we do not have terrible toruble if we ever have to add questions, however the structure of the table was deliberately left in this Unnormalized state becasue of the raw data that is uploaded. That is the data is collected with paper and pencil and is manually entered into excel. I then receive the excel spreadsheet and add the new data to my access db. The excel spreadhsset is formated exactly like the access db and the person who does the entries form the paper survey into excel simply "tabs" over in the rows entering the responses to each quesiton. I need to keep this entry procedure as simple as possible for them so I need my feilds in my main table (tbl_responses_2_0) to match the excel entry source so that it can imported easily. I cant change the format of the excel entry sources. So thats why my main dataset table is not very normalized.

    Do you present the survey taker with a choice of possible answers or are the answers free form text?
    The answers are all multiple choice with a 1-7 likert scale and 8 and 9 for dont no and no repsonse respectively. The repsonses can all be quantified into means.

    I have actually been able to figure out a siolution that works it took me a long time and allot of searching and its a convoluted Iif statemtn but the means are now caluclating properly.

    The code I had to write to have an Average Query that returns the means for all the quesitons goruped my year and quarter looks like this and is comprised of 25 Iif funcitons for each of the 25 quesitons.

    SELECT tbl_responses_2_0.YEAR, tbl_responses_2_0.QUARTER, tbl_responses_2_0.MODE, Avg(IIf([A_facilities_benches] In (0,8,9),Null,[A_facilities_benches]))
    AS A_facilities_benchesAvg, Avg(IIf([A_facilities_clean] In (0,8,9),Null,[A_facilities_clean]))
    AS A_facilities_cleanAvg, Avg(IIf([A_facilities_condition] In (0,8,9),Null,[A_facilities_condition]))
    AS A_facilities_conditionAvg, Avg(IIf([A_fleet_seats] In (0,8,9),Null,[A_fleet_seats]))
    AS A_fleet_seatsAvg, Avg(IIf([A_fleet_floors] In (0,8,9),Null,[A_fleet_floors]))
    AS A_fleet_floorsAvg, Avg(IIf([A_fleet_exterior] In (0,8,9),Null,[A_fleet_exterior]))
    AS A_fleet_exteriorAvg, Avg(IIf([A_fleet_noise] In (0,8,9),Null,[A_fleet_noise]))
    AS A_fleet_noiseAvg, Avg(IIf([A_ops_traffic] In (0,8,9),Null,[A_ops_traffic]))
    AS A_ops_trafficAvg, Avg(IIf([A_ops_speed] In (0,8,9),Null,[A_ops_speed]))
    AS A_ops_speedAvg, Avg(IIf([A_ops_crteous] In (0,8,9),Null,[A_ops_crteous]))
    AS A_ops_crteousAvg, Avg(IIf([A_ops_appear] In (0,8,9),Null,[A_ops_appear]))
    AS A_ops_appearAvg, Avg(IIf([B_safety_riding] In (0,8,9),Null,[B_safety_riding]))
    AS B_safety_ridingAvg, Avg(IIf([B_safety_waiting] In (0,8,9),Null,[B_safety_waiting]))
    AS B_safety_waitingAvg, Avg(IIf([B_safety_measures] In (0,8,9),Null,[B_safety_measures]))
    AS B_safety_measuresAvg, Avg(IIf([B_info_on_bus] In (0,8,9),Null,[B_info_on_bus]))
    AS B_info_on_busAvg, Avg(IIf([B_info_at_stations] In (0,8,9),Null,[B_info_at_stations]))
    AS B_info_at_stationsAvg, Avg(IIf([B_service_seats] In (0,8,9),Null,[B_service_seats]))
    AS B_service_seatsAvg, Avg(IIf([B_service_ovrcrwd] In (0,8,9),Null,[B_service_ovrcrwd]))
    AS B_service_ovrcrwdAvg, Avg(IIf([B_service_value] In (0,8,9),Null,[B_service_value]))
    AS B_service_valueAvg, Avg(IIf([B_routes_on_sched] In (0,8,9),Null,[B_routes_on_sched]))
    AS B_routes_on_schedAvg, Avg(IIf([B_routes_trnsfr] In (0,8,9),Null,[B_routes_trnsfr]))
    AS B_routes_trnsfrAvg, Avg(IIf([B_routes_time] In (0,8,9),Null,[B_routes_time]))
    AS B_routes_timeAvg, Avg(IIf([B_routes_dist] In (0,8,9),Null,[B_routes_dist]))
    AS B_routes_distAvg, Avg(IIf([C_loyalty_1] In (0,8,9),Null,[C_loyalty_1]))
    AS C_loyalty_1Avg, Avg(IIf([C_loyalty_2] In (0,8,9),Null,[C_loyalty_2]))
    AS C_loyalty_2Avg
    FROM tbl_responses_2_0
    WHERE (((tbl_responses_2_0.DIS)=1))
    GROUP BY tbl_responses_2_0.YEAR, tbl_responses_2_0.QUARTER, tbl_responses_2_0.MODE
    HAVING ((tbl_responses_2_0.MODE)=1))
    ORDER BY tbl_responses_2_0.YEAR DESC , tbl_responses_2_0.QUARTER DESC;
    Can there be multiple responses to a question?
    yes. they can choose 1-9 on a scale of satisfaction with 8 and 9 for dont know and no response.

    Is every respondent presented with the same questions for each survey?
    Yes

    Do you have multiple types of surveys each with their own assemblage of questions?
    I have tow slightly differnet surveys in this DB. They mostly have the same quesiton but with slight variations quesitons more or less. They often need to be analyzed together so the data needs to reside together though. They can be filtered seperatly using the MODE feild. Which you will notice in the code above. MODE 1 is for survey 1. MODE 2 is for survey 2.

    Does the same person take the survey multiple times?
    No, A repsondent has a respondent ID which is the primary key for tbl_repsonses_2_0.

    Now that you have all that background mabey you can help me with my next quesiton.

    No that I have this Query built that retuns the correct averages I would like to filter out responses my Market Segment using the exact same feilds and criteria as the average query whos code is shown above (called Avg_Query) . We have 6 market segments. and to return the same mean results for each of the market segments I built 6 new queries that are exactly the same as the one above except they each have 1 more feild added to it that fulls out the data filtered by Market Segment.

    Now, I have 6 new working queries for each segment that return the mean responses to every question for that segment. They are gourped by YEAR and by QUARTER.

    Now, I would like to be able to enter a range of years somewhere simple (ex: 2009 quarter 1 - 2010 quarter 3) and when I do so all 6 of these queries will update to return data just based on that time frame.

    Right now I have to go into every single one of the 6 queries seperately and under the YEAR feild in design view under criteria I add a between statement (ex.between 2009 and 2010) and under the QUARTER feild add (ex: between 1 and 3) So I need to repeat this for all six.

    What I would love to be able to do is just enter this criteria somewhere one time! And in doing so all 6 of these queries will be tied to that place and they will all update as specified by YEAR and QUARTER to return data from just the specified time frame.

    I don;t know if a FORM can do this but if so how the heck would that be done. I have heard a Parameter or Append queiry would work but dont know how either.

    Any suggesitons? Sorry for the length of this repsonse. Theres allot of info here. Thank you so much for your continued help.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    That is the data is collected with paper and pencil and is manually entered into excel. I then receive the excel spreadsheet and add the new data to my access db. The excel spreadhsset is formated exactly like the access db and the person who does the entries form the paper survey into excel simply "tabs" over in the rows entering the responses to each quesiton.


    What you need to do is to import the spreadsheet into Access and then use append queries to pull the data out of the spreadsheet and put it into the correct table structure. This can be done automatically with VBA (Visual Basic for Application) code, but that would be another discussion.

    I cannot stress strongly enough that you must fix your table structure. BTW when you fix your table structure, your queries will be much easier to create!

    An unbound form can be used to input the dates. The queries would have to reference the form controls that hold those dates in the WHERE clause of the query. This type of query would be considered a parameter query since you need to supply parameters (the dates) in order for the query to return any results. You will have to revise each of your existing queries accordingly. The WHERE clause would look something like this:

    WHERE yourdatefield BETWEEN forms!yourformname!startdatecontrolname AND forms!yourformname!enddatecontrolname

    That is all of the help I will provide with your current table structure; if you decide to redesign your database, as I have strongly recommended, I will be more than willing to help. I would also be willing to help you migrate your existing data into the new table structure. My apologies for being so blunt, but having the correct table structure is critical and assisting you in perpetuating your current system would be a disservice to you.

  5. #5
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    Please no need to apologize. I really do appreciate your suggestions and I am sure that you are right. My first thought when you mentioned the append queries and the ability to automate them with VBA is that I need to do just that. This is going to take time because I kind of stumble along through Access as i learn. Im on a tight time frame of about of month for the entire project and this DB is just a very integral PART of the whole thing. So, if I can make it happen I am going to try my best to do as you say and make the quesitons records not feilds. I just dont really no how yet!? But thanks for you suggesitons and you offer to help. It is much appreciated. Let me see what I can do to get my DB closer to what you suggest and then I would greatly appreciate it if we could continue this conversation. Your advice is sound and very helpful. I especially like your solution of using append queries combined with VBA but I have no knowlege of how those work so that will take a big learning curve to get going. But the added ability to make my queries much easier is going to pay off in the long run if Im able to make it happen. If I get it structured properly I would love to draw on your assistance in getting my raw data to migrate properly. Thanks for the offer. I will gladly take you up on that once I am able to figure out just how to restructure this thing and if I have the time to do it!

    Cheers

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'm here to help when you need it!

    Based on how you responded to my questions, the following would be a preliminary structure.

    A table to hold the questions

    tblQuestions
    -pkQuestionID primary key, autonumber
    -txtQuestion


    A table to hold the possible multiple choice responses

    tblPossibleResponses (9 records corresponding to the choices you mentioned)
    -pkPResponseID primary key, autonumber
    -txtResponseText
    -longResponseRankValue

    A table to hold the participants

    tblParticipants
    -pkParticipantID primary key, autonumber
    other fields

    Since you have more than 1 survey version, a table to hold that

    tblSurveys
    -pkSurveyID primary key, autonumber
    -txtSurveyName

    Now a table to relate the appropriate questions with each particular survey

    tblSurveyQuestions
    -pkSurveyQuesID primary key, autonumber
    -fkSurveyID foreign key to tblSurveys
    -fkQuestionID foreign key to tblQuestions



    Now you can use the tblSurveyQuestions to actually pre-populate the appropriate records for a particular survey in preparation for the responses. This would be similar to a template.

    Now associate the participant with the survey they are taking

    tblParticipantSurvey
    -pkPartSurveyID primary key, autonumber
    -fkParticipantID foreign key to tblParticipants
    -fkSurveyID foreign key to tblSurveys
    -dteSurvey (date of survey)

    Earlier, I asked if a person can provide multiple responses for a question and you said yes. I just wanted to be clear, for 1 question a person can provide more than one multiple choice answer of the 7 multiple choices presented to them? If that is the case the structure will be a little different compared to whether the participants can provide only 1 answer.

    If a participant can only provide one response to a question, the structure would look like this:

    tblParticipantSurveyQResponses (the records in this table can be pre-populated as indicated earlier once the participant is set up with the appropriate survey; once prepopulated, the respondent just selects their response for each question)
    -pkPartSQResponseID primary key, autonumber
    -fkPartSurveyID foreign key to tblParticipantSurvey
    -fkQuestionID foreign key to tblQuestions
    -fkPResponseID foreign key to tblPossibleResponses

    I'm not sure how the market segment thing fits into this. We can discuss that when you are ready to do the redesign.

  7. #7
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    Thanks again for your repsonse. I am going to go over this thread and your suggestions with my coleague. This REALLY helps. After we discuss I may be in touch again. I'm going to go ahead and get started on this restructuring first thing tommorow morning. You and eveyone else Ive talked to have made it clear that it is essential. I do have one important question in the mean time. You mentioned that I could append my imported raw data from excel to its appropriate table location using append queries and VBA to automate the process when I import to Access. Would it be alright, if instead of building in the VBA and append queries to make the data go to the right table I could keep the (rather flat un-normalized table) that I have been using so far to bring in the Excel data and then from there I would build the nessesary table to normalize it all from that source? Or do I need it all to go strait from excel into this new normalized format? My concern as you pointed out is the ability to add more quesitons in the future. I am guessing that keeping the flat unormalized import table that I use now is not a good idea for that reason right?

    Anyway, thanks again for pointing me in the right direciton. I hope to be in touch soon with the right structure and draw on your expertice for importing the data correctly.

    Kevin

  8. #8
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    MABEY YOU CAN HELP ME DECIDE WHAT TABLES TO BUILD AND HOW BEST TO STRUCTURE (NORMALIZE THIS)

    YES I think that it would probably be best for communication if I show you how our table fields are set up right now. (The data we have to work with and analyze) I hope its not too much info but I really value and need your advice so I will include it in case it helps.

    FYI: Its probably best to disclose for communication purposes that this survey is for people who used our service (BUSES) and is a survey of customer satisfaction and what they think of our service)

    *Note we had a few versions of the same survey. We were analysiing these in excel until it got out of hand. The "survey version" is the first field. (version A was old, version B was new [added quesitons] version A/B is the latest and we added a few more quesitons

    | survey_version | Respondent_ID (Primary Key) | agent_name (person who administered the survey | MODE (Survey 1 or survey 2 [we have 2 surveys in here] )

    Then comes...

    | started_at_date | started_at_time | finished_at_time | time_taken_in_minutes |

    Then comes a series of Screener Questions if they answered yes their responses are not used in the “mean” analysis but their demographics are kept for other uses. Yes or No

    Screener_Q_1 (yes or no are you old enough to take survey) | screener_Q_2 (yes [1] or no [2] are you an employee of the company) | year_of_birth |

    Then some question on what routes they traveled which can have a 1-200 response that relate to 1 of 200 possible routes)

    | route_1_bus (what route did you take 1-200) | route_2_bus (what route did you take 1-200) | route_3_bus (what route did you take 1-200) |


    The next three questions [priorities] are where they rank their top 3 priorities and those 3 use a different scale than the rest to rank what 2 things are most important to them for good service)

    Q1_priority_1 | Q2_priority_1 | Q3_priority_3 |

    The next MAIN 25 questions are the bulk of the essential data. They all use the same 7 point “Likert” scale to judge their satisfaction level with 8 for don’t know and 9 for no response) These are the questions where we analyze the mean responses to each one.

    Q1 | Q2 | Q3 | ….Q25 |


    Then we have a question that asks Yes [1] or No [2] if they had any additional concerns)

    | Q_concerns |

    Then we coded their concerns to categorize them into 5 service areas that they best fit with i.e. safety, fleet, driver, etc)

    | code1 | code 2 | code |

    After that we have a text field where we store the answer to the “concerns” question verbatim

    |Q_Verbatim |

    then we have a whole section on what the weather was like that day and the location of the interview that looks like this

    | location | weather | temperature |

    …then comes the section of "when" that the survey administrator fills out that is derived partly from the date and time fields at the start

    MONTH | YEAR | QUARTER | DAY| WEEK | TIMEOFDAY |

    …then comes the section on the respondents demographics and their market segment that looks like this

    AGE (Derived from the birth date field) | SENIOR | YOUTH | POSTSECONDARY | ...etc | These again are yes and no [1or2] records that say if the respondent is one of these that field gets a 1 if not it gets a 0)

    So we have a 1-200 scale for routes, we have a 1-9 scale for the main quesitons, we have a 1-5 scale for priorities, we have a bunch of different yes or no q's, we have 1 text feild for the verbatim repsonses to the concerns, then we have the scale to record the "coded" "service area" categories where their open ended concern would fall. Then we have field like AGE where any number can be, and YEAR and Date of Birth where allot of different numbers can be,

  9. #9
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    Sorry, forgot to mention. The respondents can in fact only provide 1 response to each question NOT multiple responses. Just select a rating on the scale. But as mentond we have a few scales for answers for a few different questions...

  10. #10
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I figured that things might be more complicated, so here goes.

    Regarding this...
    | survey_version | Respondent_ID (Primary Key) | agent_name (person who administered the survey | MODE (Survey 1 or survey 2 [we have 2 surveys in here] )
    The table structure I proposed earlier handles multiple survey types, so that is not an issue.

    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


    Since you have more than 1 person associated with a survey (agent and participant) that describes a one (survey)-to-many (people). Similarly since you have 2 date/times (Start and end) associated with a survey that should be handled in a similar fashion

    tblParticipantSurvey
    -pkPartSurveyID primary key, autonumber
    -fkSurveyID foreign key to tblSurveys


    tblParticipantSurveyPeople
    -pkPartSurveyPeopleID primary key, autonumber
    -fkPartSurveyID foreign key to tblParticipantSurvey
    -fkPeopleID foreign key to tblPeople
    -fkPeopleTypeID foreign key to tblPeopleType

    tblParticipantSurveyDates
    -pkPartSurveyDatesID primary key, autonumber
    -fkPartSurveyID foreign key to tblParticipantSurvey
    -dteSurvey (best to have this set up as a date & time field not just one or the other)
    -fkDateTypeID foreign key to tblDateTypes

    tblDateTypes (2 records start and end)
    -pkDateTypeID primary key, autonumber
    -txtDateType

    The time_taken_in_minutes field is a calculated value and should not be stored in a table. It can be calculated on the fly whenever you need it.


    Next you discuss various groupings or categories of questions (screener, priority...). You also have a question that does have multiple answers, i.e. the route question--there can be many routes and each are identified; so from 1 question--many answers.

    So we have to identify each question as to its category

    tblQuestions
    -pkQuestionID primary key, autonumber
    -txtQuestion
    -fkQuestionCatID foreign key to tblQuestionCategory

    tblQuestionCategory
    -pkQuestionCatID primary key, autonumber
    -txtQuestionCategoryName

    Now that at least 1 question will solicit multiple responses that changes our participant survey question & response table structure.

    tblParticipantSurveyQuestions
    -pkPartSQResponseID primary key, autonumber
    -fkPartSurveyID foreign key to tblParticipantSurvey
    -fkQuestionID foreign key to tblQuestions

    Since at least one question has to accept free form text we'll need 2 fields, one for the possible predetermined responses and the other for the free form text

    tblParticipantSurveyQuestionResponses
    -pkParticipantSurveyQRID primary key, autonumber
    -fkPartSQResponseID foreign key to tblParticipantSurveyQuestions
    -fkPResponseID foreign key to tblPossibleResponses (the predetermined responses)
    -txtFreeFormTextfield

    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


    Regarding:
    MONTH | YEAR | QUARTER | DAY| WEEK | TIMEOFDAY |
    This can be extracted from the survey date/time fields so there is no need to store it in a table. If you capture the date of birth of the participant, there is not need to store the age--it can be calculated.




  11. #11
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    Okay! Here I go. Im going to need some time to get started on building this. Thanks you for providing me the foundaiton to start building this. Do you think its going to be much tourble to do the data import from excels flat file? The excel file looks the same as my main tbl_responses_2_0 format that you just told me how to structure properly. Is there allot of coding and work invoved in making the data import work properly so that the records go to the right place in all these tables? I only ask because I have some time constraint factors for getting this to market.

    THANKS!

  12. #12
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The importing is easy. You would import the Excel data into a table in Access (takes a couple minutes to set up and run). The more difficult part is copying the data out of the Excel data table into the structured tables. Generally, it can be done with a series of append queries executed in a specific sequence. You would only need to create the queries once and then just run them thereafter, so there will be some up-front work involved in creating and testing those queries to make sure they work properly; afterward it is just a matter of running them. Of course, if you change your Excel format, you will have to start over!

    You can actually do the queries without any Visual Basic for Application coding. The code would be used to automate the process, but it is best to layout and test the append queries manually first, before converting them to code.

  13. #13
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    The importing is easy. You would import the Excel data into a table in Access (takes a couple minutes to set up and run).
    In other words we would continue to import into our our original tbl_responses_2_0 table. The one we have been using all along and then we would build append queries off of IT to populate the new "normalized" table structures?

    Of course, if you change your Excel format, you will have to start over!
    So, in other words am I right in inffering that this restructuring and normalization will not actually change the problem of adding more questions in the future.? If our raw data input is continued using Excel and we decide to add more quesitons we will be adding new columns to Excel and then we would need to add the same "feilds" to correspond in our main Access based "import table"

    If we made these changes to the Excel data input source and then the main import table in Access would these queries function properly?

    If not is this the right solution? We need the funtionality of adding questions in the future! The whole idea of restucturing was based on two factors, easy of query design and analysis in Access for averages and counts, and secondly the abilty to add questions in the future!

  14. #14
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The redesigned table structure I have proposed will be able to readily handle additional questions. Over the long term, I would recommend going to Access forms for your data entry rather than the Excel. Based on your earlier posts, it was apparent that you were under time constraints, so the quickest solution for right now was to just import your current spreadsheet and run the append queries until such time was available to create a new user interface (i.e. forms) in Access.

    If you create append queries now based on your current Excel format they should be fine. But, if you change your Excel format, you would have to rework those queries (the underlying table structure I proposed would remain unchanged).

    Any other queries that you create based on the underlying table structure I have proposed for doing your averages & counts etc. would not have to be changed since they would rely strictly on the tables and are independent from the Excel data table you would import.

    Hopefully this is clear.

  15. #15
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21
    Right on. Thanks. I think I will build the structure much like you say and then append the data from tbl_repsonses_2_0 into the newly structured tables and then build an Access form to do the inputs with for our next batch of surveys. The form interface seems like the right way to go. Thanks agian for your support and for putting me on the right course. I have some work ahead of me now but at least Im on the right track!

    Cheers,

    Kevin

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reporting counts in another query...maybe?
    By Geewaagh in forum Queries
    Replies: 7
    Last Post: 06-04-2010, 07:39 PM
  2. Sum bookings by quarter
    By kgav1 in forum Access
    Replies: 3
    Last Post: 04-14-2010, 08:15 PM
  3. Query showing averages for groups
    By AnthonyTesta in forum Queries
    Replies: 1
    Last Post: 02-03-2010, 09:04 PM
  4. Missing Counts that = 0 in query results
    By dandhjohn in forum Queries
    Replies: 1
    Last Post: 01-29-2010, 11:28 AM
  5. Query to extract record of particular year
    By pkg206 in forum Access
    Replies: 2
    Last Post: 11-11-2009, 10:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums