Solved

MS Access Compound Query

Posted on 2014-03-16
2
595 Views
Last Modified: 2014-04-01
Within an MS Access database, I need to filter for unique values in a single table and then produce a count for how many times each of those unique values appeared in the table.

The end goal is a single form in datasheet view that lists each unique value, its current record count, and the target sample size.  I can then apply the appropriate conditional formatting.

I have two tables.  

"table_questions" simply lists all the various questions ('question') in a survey and a number ('sample_size') representing the ideal sample size

"table_reponses" recorded responses to each survey question. The two columns are 'question' and "response'

So basically, I want to create a form in datasheet view that lists the unique questions in the "table_responses" table, count and report each time that question was in the table, and compare that number to the "sample_size" using the "question" as the primary key.

Any thoughts?
0
Comment
Question by:chaddcarr
2 Comments
 

Author Comment

by:chaddcarr
ID: 39933008
As an update, I figured out how to filter for distinct questions only, and produce a count for each:

SELECT
    Question
  , COUNT(*) AS count_distinct_questions
FROM
    ( SELECT DISTINCT
          Question
      FROM tbl_question_count
    ) AS tmp
GROUP BY
    Question;

Now I need to use the Question to query the table_questions table to get the targeted sample size...
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 100 total points
ID: 39933031
Select question, Count(*) As Response_count, sample_size
From table_questions Left Join table_Responses On table_questions.Question = table_Responses.Question
Group by question, sample_size;

This query should get both of the numbers you need.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
bind Combobox 4 29
vba DCount with 2 criteria 3 33
How can I create a table in a linked database, in VB? 7 17
Excess tables to Excel BackUp 3 28
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now