|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access Survey Lessons Welcome to the Access Survey Seminar. Total running time is 4 hours, 6 minutes. Lessons
Course FilesLinks
Resources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn this seminar, you will learn how to create and manage surveys using Google Forms, collect responses, and efficiently import the data into Microsoft Access for advanced reporting and analysis. I will show you methods for importing data both through Microsoft Excel and directly via VBA code, demonstrate how to handle multiple response questions, and walk you through building queries and custom forms to analyze respondents and their answers. We will also cover setting up relational tables, managing parsed responses, and designing reports that display single and multiple responses for deeper insights.TranscriptWelcome to the Microsoft Access Survey Seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this seminar, I will teach you how to create surveys and collect data from multiple respondents using Google Forms, a free online service. Once the information has been collected, I'll show you how to get it using Microsoft Excel, automated from Access. Then I'll also show you how to grab it as a CSV file directly from Access without using Excel. There are two different options you can use to get the data into Access. We'll make all kinds of different queries to do all kinds of different reporting, and then we'll make a couple of different forms so you can see each respondent and their responses, or the other way, see all the responses and who responded that way. We'll do grouping reports and lots more. Now, like it shows right here, this is a developer level seminar. Access is not for beginners, so you should definitely know your way around Access. I strongly suggest you take my beginner and expert series before this class, and I highly recommend Developer 1, which teaches you VBA, and Developer 16, which covers record sets. I will use a lot of record sets in today's class. At a bare minimum, you should have at least watched my Intro to VBA free video on my website. I will be using Microsoft 365. It's currently November of 2021, so this is roughly equivalent to Access 2019. Most of what I'm going to show you in this video should be valid going all the way back to Access 2007 or later. If you have questions about the material covered in today's class, you can post them in the comments or questions section down at the bottom of the page that you're on right now. If you have unrelated questions regarding Access in general, you can post them in the Access forum. Let's take a closer look at exactly what we're going to cover in today's class. In lesson one, we'll see how to create a survey using Google Forms, a free service online. We'll learn how to collect the responses in a spreadsheet and then import that information into Microsoft Access. Lesson two is taken from the extended cut from my TechHelp for Silver Members. So if you're already a Silver Member and you've watched this already, you can skip lesson two. In lesson three, we're going to learn how to bypass Excel completely and use just Microsoft Access VBA to download our survey data direct from the web as a CSV file, a text file. Then we'll import that information directly into Access with a linked table. All goodies from the code vault coming up in lesson three. In lesson four, we're going to work on importing the data that we downloaded in the last lesson into our database. This way we can set it up properly and make it relational. We can add the supporting data to a helper data table, which I'll talk about in a few minutes, and make a button to delete the data. Lots more is covered in lesson four. In lesson five, we're going to continue with importing data. We're going to tackle the multiple response questions, those checkboxes. We're going to parse the comma separated lists that Google gives us and put each one of those items in its own record in the table. We'll add them to the helper table if needed. Then we'll add another question or two to the survey and see how we have to update the linked table specifications every time we add a question. That's all covered in lesson five. In lesson six, we're going to work on querying our data. We'll put a bunch of different queries together to deal with the data that we've just imported into the database. We'll see a list of duplicate respondents if someone responded more than once to the survey. We'll look at surveys with the helper data attached so you can see not just the ID, but the information as well. We'll also look at non-customer responses, so who responded to the survey who is not a customer, and customers who have not responded. You send it out to a bunch of customers; who hasn't responded yet? Then we'll do popular response counts. In lesson seven, we're going to make some forms so we can view this data in the database. We're going to make a survey list form. You can see a list of all the surveys sent in with the date and the email, whether or not that person was a customer, and you can double-click on the customer ID to open up the customer form, or you can double-click on the survey ID to open up the survey form. We'll put a Show Survey Responses button on the customer form. If they don't have any survey responses, we'll disable that button. We'll make the survey form. We'll show the combo boxes for each response that are single responses. We'll make a multiple response list so you can pick what list you want to see, like the Star Trek list or whatever, and it will show you their responses in a subform. A lot to do in this lesson. In lesson eight, we're continuing with survey forms. This one's a little tricky, but I think you will like the results in the end. What we're going to do is go backwards. I don't want to open up the respondent, the person, and see their responses. I want to go the other way. I want to say, show me a list of all the questions and the responses to that question, and then I want to see all the people who responded to that. For example, here I did Star Trek, who's your favorite Star Trek person? Q, these people responded to that one. The benefit of this is that you might want to do marketing to a specific response from a specific question, like, are you planning on mortgaging your house in the next five years? So show the mortgage or house question, who answered five years, and show me those people. We're going to do that in this lesson. In lesson nine, I'm going to show you how to make a report with sorting and grouping levels for our surveys with multiple responses. A lot of the reports that you can build are simply just building the right query and then turning the query into a nicely formatted printable report, which we've done with a lot of the queries that I can think of generating for this type of survey data. But one thing I haven't shown you in this seminar, at least, is how to make a report that will show the multiple responses. So in this lesson, I'll show you how to do that. QuizQ1. What online tool is recommended in this seminar for creating surveys?A. Google Forms B. Microsoft Word C. SurveyMonkey D. Access Web App Q2. How does the seminar explain to collect responses from survey respondents? A. Use Google Forms to collect responses in a spreadsheet B. Collect responses manually in a Word document C. Use printed forms and scan them D. Use email replies Q3. What are the two main ways mentioned to get survey data into Microsoft Access? A. Importing through Excel or as a CSV file directly with VBA B. Manually entering data into Access or using XML files C. Using PDF exports or Access macros D. Copying data from Outlook emails Q4. What skill level does this seminar assume the viewer has with Microsoft Access? A. Developer level; not for beginners B. No prior Access experience C. Only knowledge of Excel is needed D. Basic Word experience is sufficient Q5. Which previous courses does Richard strongly recommend before taking this seminar? A. Beginner, Expert series, Developer 1, and Developer 16 B. Only the Beginner Access course C. Microsoft Word Advanced D. Google Forms for Beginners Q6. What is covered in lesson three of the seminar? A. Downloading survey data as a CSV file using VBA B. Designing surveys in Access directly C. Printing reports from Access D. Writing SQL queries for Excel Q7. What does lesson five teach you to handle? A. Parsing multiple response (checkbox) questions into individual records B. Formatting form layouts C. Securing Google Forms with passwords D. Sending automated email responses Q8. What is the primary focus of lesson six? A. Building queries to analyze survey results B. Embedding videos in surveys C. Creating PowerPoint presentations D. Developing macros in Word Q9. What functionality is added to the customer form in lesson seven? A. A Show Survey Responses button, which is disabled if absent B. Live chat support C. Automatic report generation on every form load D. A weather widget Q10. What unique reporting method is demonstrated in lesson nine? A. Reports with sorting and grouping for multiple responses B. Automatic graph generation in Excel C. Importing images into reports D. Embedding a survey directly in a report Q11. What advantage does the alternative approach in lesson eight provide? A. Shows all questions with their responses and who answered each B. Generates random survey questions C. Blocks duplicate entries automatically D. Decodes encrypted responses Q12. Which action is NOT discussed as part of the lessons in the seminar? A. Adding responses to helper tables B. Converting surveys to PDF forms C. Handling duplicate respondents D. Updating linked table specifications Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on the Microsoft Access Survey Seminar. I'm Richard Rost, your instructor, and in this seminar, I'll walk you through building survey tools and managing response data using Microsoft Access in combination with Google Forms and Excel.We'll begin by exploring how to design and deploy surveys using Google Forms, which is a free tool available online. I will show you the process for collecting responses through Google Forms, saving that data in a spreadsheet, and then importing it into Access. I'll also explain two different ways you can transfer the responses into Access: either by automating the process through Excel or by downloading the raw CSV file and importing it directly into Access using VBA. The seminar covers a wide range of Access techniques, primarily focusing on advanced queries and custom forms for reporting and reviewing survey results. You will learn how to design forms that allow you to view individual respondents and their answers, or look at each question and see which respondents gave particular answers. We will also discuss how to group and summarize responses through various reporting options. This seminar is intended for advanced Access users and is not recommended for beginners. You should have solid experience with Access, ideally having completed both the beginner and expert instruction series. I strongly recommend familiarity with Developer 1, which covers VBA programming, and Developer 16, which focuses on record sets; these concepts are used extensively throughout this class. At the very least, you should watch the Intro to VBA video on my website if you are not already comfortable with basic VBA. I am demonstrating these concepts using Microsoft 365 as of November 2021, which aligns closely with Access 2019. The majority of the material will also apply to Access versions 2007 and later. If you have questions specifically about the seminar content, you can leave them in the comments or questions section below the page. For unrelated Access questions, please use the Access discussion forum. Here is the structure we'll follow today: In the first lesson, I'll guide you through creating a survey using Google Forms and show how to collect responses in a spreadsheet and bring them into Access. Lesson two is part of my TechHelp series for Silver Members. If you have access and have already covered this material, you may skip this lesson. Lesson three focuses on bypassing Excel entirely. We will use VBA in Access to fetch survey data directly from the web as a CSV file and import it into Access using a linked table. In lesson four, we will organize the newly imported data, make it relational, and use a helper data table to add supporting records as needed. You'll also learn how to automate deleting data with a simple button. Lesson five continues the data import process by handling questions with multiple responses, such as those in checkbox format. I will demonstrate how to parse Google's comma-separated lists and store each entry in its own record. We will also update linked table specifications whenever changes occur in the survey structure. Lesson six is all about querying. We'll perform a variety of queries, like identifying duplicate respondents, linking survey responses to helper data for enhanced information, filtering for non-customer respondents, and finding customers who have not yet completed the survey. We'll also look at counting the popularity of different responses. In lesson seven, I'll step you through building forms to assist with reviewing and managing your survey data. For example, we'll set up a survey list form to view all submitted surveys, including dates and related customer data. There will be navigation features such as opening customer and survey details with a double-click, and a button to display a customer's survey responses, which is disabled if they have not participated. We'll create forms that present both single and multiple response questions and allow users to review collected data in a comprehensive way. Lesson eight will address a different view of the data: Instead of seeing each individual respondent and their answers, I'll show you how to display each question and then see the list of people who chose each particular answer. This approach is especially useful for marketing purposes, allowing you to target respondents based on specific answers to key questions. Finally, lesson nine demonstrates how to produce a report featuring sorting and grouping of survey data, especially for questions with multiple responses. We'll go beyond simple list reports and construct one that displays all selected responses together, allowing for a clear overview of trends in the data. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListCreating a survey using Google FormsCollecting survey responses in a spreadsheet Importing Google Forms responses into Access via Excel Automating data retrieval from Excel into Access Downloading survey data as CSV using Access VBA Importing CSV data into Access with linked tables Structuring survey data relationally in Access Using a helper table for supporting survey data Deleting imported data via button in Access Parsing multiple response questions from comma lists Storing individual responses from checkbox questions Updating linked table specifications after adding questions Creating queries for duplicate survey respondents Querying surveys with attached helper data Identifying non-customer survey respondents Finding customers who have not responded Counting popular survey responses Building a survey list form in Access Creating navigation buttons between surveys and customers Disabling buttons when no survey responses exist Displaying single and multiple responses on forms Showing multiple response lists in subforms Designing forms to view responses by question Filtering respondents by specific answers Generating reports with sorting and grouping for surveys Building printable survey reports showing multiple responses |
||
|
| |||
| Keywords: access seminar survey PermaLink How To Create, Import, Query, and Report on Survey Data with Google Forms and VBA in Microsoft Access |
