Counting 04-26-07

Hello,

I am having difficulty counting numbers within a very large table based on
survey results.

The table is set as follows subjectname, q1a, q1b, q1c, q1d...q5e
Values for each question range from 1-5 and the field could be empty if no
response was given.

I have tried the something similar to the following and it is not counting
correctly.

Select subjectname, count(iff(q1a=5 or q1b=5 ... or q5e=5, 1, 0))
from table
group by subjectname

My desired outcome will be to display the total 5's, Total Responses for the
subject (count where the response is in 1-5), and from this I can calculate
percentages within each rating (15% of participants rated the subject as a 5)

Any help is appreciated. Thanks!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1

0
andy_42
4/26/2007 4:49:26 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
839 Views

Similar Articles

[PageSpeed] 45

Andy,

First off, your data is in the wrong format for this kind of analysis.  You 
are setup like a spreadsheet, not a database.  Questionnaire databases should 
have a structure similiar to:

Subject Question Response
Ted       Q1            5
Ted       Q2            3
Ted       Q3            4 
Ted       Q4            5
Ted       Q5            3

With your data in this format, you can do a simple crosstab query with the 
subjectName as the RowHeader, the Response as the Column header, and 
Count(SomeField) as the Value.

To get your data into this format, you could write a Normalization query 
that would normalize your data.  It would look something like:

SELECT SubjectName, "q1a" as Question, q1a as Response FROM yourTable
UNION ALL
SELECT SubjectName, "q1b", q1b FROM yourTable
UNION ALL
SELECT SubjectName, "q1c", q1c From yourTable

This is a union query, and can only be written in the SQL view, although I 
would start out in the query grid to get the first select statement.  If you 
have a lot of fields, it will take a while to build this query, but once you 
have done so, you can save it.

HTH
Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"andy_42 via AccessMonster.com" wrote:

> Hello,
> 
> I am having difficulty counting numbers within a very large table based on
> survey results.
> 
> The table is set as follows subjectname, q1a, q1b, q1c, q1d...q5e
> Values for each question range from 1-5 and the field could be empty if no
> response was given.
> 
> I have tried the something similar to the following and it is not counting
> correctly.
> 
> Select subjectname, count(iff(q1a=5 or q1b=5 ... or q5e=5, 1, 0))
> from table
> group by subjectname
> 
> My desired outcome will be to display the total 5's, Total Responses for the
> subject (count where the response is in 1-5), and from this I can calculate
> percentages within each rating (15% of participants rated the subject as a 5)
> 
> Any help is appreciated. Thanks!
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1
> 
> 
0
Utf
4/26/2007 5:16:08 PM
Reply:

Similar Artilces:

crystal reports 9.2.2 02-03-04
I have been working on customizing reports. After I upgraded crm to 1.2 I can no longer access my reports. I tried to install the crystal repors enhancement for 1.2. I am getting an error that I am using crystal reports 9.2.0 and I need to upgrade to 9.2.2? I had crystal reports 9.0 professional edition installed on my computer also. I removed that and then tried to install the crm 1.2 crystal reports enhancement and I am getting the same errors. Please help. Thank you It seems that CRM requires a very specific version of crystal reports ie 9.2.2 Microsoft have told me that you ...

Windows mail problem 07-16-10
Tried to log into my optonline account and received the following message: The connection to the server has failed. Account: 'mail.optonline.net', Server: 'mail.optonline.net', Protocol: POP3, Port: 995, Secure(SSL): Yes, Socket Error: 10060, Error Number: 0x800CCC0E Can anyone decipher this for me? What is the issue? You should not be using advanced ports or SSL, see http://optimum.custhelp.com/cgi-bin/optimum.cfg/php/enduser/std_adp.php?p_faqid=2397&p_created=1237397986&p_sid=ZbGnw*4k&p_accessibility=0&p_redirect=&p_lva=&p_sp=cF9zcmNoPSZ...

Counting how many different names in a long list
I have a list of 332 names many of them are duplicated. I am looking for a formula or function that can count how many unique names there are in the list. Can anyone help? -- Quaisne ------------------------------------------------------------------------ Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052 View this thread: http://www.excelforum.com/showthread.php?threadid=501357 This'll count the number of distinct values in a range: =SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&"")) (adjust the range to matc...

Windows installer problem 04-08-10
Hi All, After login to windows xp, on screen displayed windows installer and nothing showing on screen .Any reply will be appreciated. Best regards Anji Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2) when posting in a forum or newsgroup. Please do so in your next reply. Was the computer fully patched at Windows Update when this problem started? What anti-virus application or security suite is installed and is your subscription current? What anti-spyware applications (other than Defender)? What third-party firewall (if any)? Has a(another)...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

HELP! 12-06-07
Excel won't open and I've both tried re-installing it, and rebooting what should I do? On Thu, 6 Dec 2007 00:42:20 -0500, avarage wrote: > Excel won't open and I've both tried re-installing it, and rebooting what > should I do? You have posted this message to the wrong newsgroup. The access in this groups name refers to Microsoft Access, a database program. Please repost to the correct newsgroup for whatever Office program you are using. I would suggest you include your Windows and Office version number in the message. -- Fred Please respond only to this newsgrou...

Report Server Error 09-19-07
I have completed my CRM install but each time I attempt to access the Report Manager I get the error message: The Scriptbibliotek '/aspnet_client/system_web/1_1_4322/WebUIValidation.js' cannot be found. Manually move the data or use the command 'aspnet_regiis -c' in order to install new. Sorry if the error is not completely correct but I´m translating from German. I have run the command 'aspnet_regiis -c' and received the message that the version started and then finished but every time I go back to Report Manager the same error pops up. Need a little...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

Option Buttons 06-21-07
If I choose a Control Source I can get the option buttons to work. Can you help? -- Dick Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1 Do you want us to push the button? What is the problem? UpRider "richardlafrance via AccessMonster.com" <u31432@uwe> wrote in message news:740b97593dc91@uwe... > If I choose a Control Source I can get the option buttons to work. Can > you > help? > > -- > Dick > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/ac...

Credit card processing 05-15-07
Is it possible with Access? Any good references? Matt <mdw233psu@yahoo.com> wrote: >Is it possible with Access? Not without putting your data in a SQL Server database among many other things. The credit card companies want to ensure your corporation is keeping that data very, very secure. And depending on the laws in your province, state, territory and/or country your employer really wants to keep that data secure. It is very embarrassing to hear your corp name on the news. >Any good references? No idea. Tony -- Tony Toews, Microsoft Access MVP Please respond only i...

Count If.....help
I am trying to sum the amount of time two words "high" and "very high" appear in a col....I can get this to work for one word but when i try and nest the statement it doest work - Why and how to i solve it. =COUNTIF('(R2) Risks Log'!L9:L99,"Very High",CountIF(Risks Log'!L9:L99,"High")) thanks. First, your question is ambiguous. Are you tring to count the number of times a cell's contents - as a whole - is either "High" or "Very High"? Or are you looking to count how many times the a) word or b) phra...

Displaying Total Word Count on Cover Sheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I want to display/show the Total Word count of my document on the Cover Page, when I print my document is is there on the Cover Page. How can I do that? Is there an Auto Text or something like that that I can use? Please anyone reply please. You can use Insert> Field to insert the NumWords Field from the Document Information category. However, the field does not update automatically. You need to update it manually by clicking in the field & pressing F9, by Control/Right-Clicking the Field & selecting...

Creating Reports 05-08-07
I have been investigating the different methods of creating a new report in crm and have found a few different methods. What is the easier and time efficient method for creating a new custom report? On May 8, 3:26 pm, Nate <N...@discussions.microsoft.com> wrote: > I have been investigating the different methods of creating a new report in > crm and have found a few different methods. What is the easier and time > efficient method for creating a new custom report? Pre-filtering the reports per the SDK is probably going to be your best bet. You can upload them into CRM and ...

Stats microsoft.public.windowsxp.general (last 7 days) 06-07-10
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: microsoft.public.windowsxp.general Stats Were Taken..........: Mon, 07 Jun 2010 08:14:28 GMT Stats Begin...............: Mon, 31 May 2010 08:29:11 GMT Stats End.................: Mon, 07 Jun 2010 06:42:42 GMT Days......................: 7 Total No. of Articles.....: 526 Total No. of Characters...: 1263268 Total Volume..............: 1233 Messages Per Day..........: 75.1 Characters Per Day........: 180466.9 Average Daily Volume......: 176 kB Total Posters This Week...:...

query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The criteria is based on data which is entered into a form. >=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate])) The true side of the IIF allows for all received dates if no data is entered into the form. I would like to instead not do a criteria test at all if there is no date entered into the form. I've tried things like "" or NULL in the criteria, but to no avail. The reason for wanting to do this, is that I have two tables linke...

Count with Hidden cells
Is there a way to count the number of text filled cells excluding the cells that I have hidden? Depends on the version of excel, excel 2003 works like =SUBTOTAL(103,A1:A10) won't count hidden cells -- Regards, Peo Sjoblom (No private emails please) "Stretch" <Stretch@discussions.microsoft.com> wrote in message news:9F387E47-FC1F-4AEB-8A6C-2B37BC012DD1@microsoft.com... > Is there a way to count the number of text filled cells excluding the > cells > that I have hidden? What does the 103 stand for? "Peo Sjoblom" wrote: > Depends on the v...

Email to a Queue 02-22-07
Hi All Is there any way that I can tell what time an email entered one of our queues? Kind Regards Peter ...

CRM SDK 07-02-03
BIG stupid question here........when you guys refer to the "SDK", what exactly are you talking about? I thought it was just the .chm help file, but after that last thread you mentioned the proxy .dll file. Where is this file? I only have the latest .chm help file and nothing more (well obviously I have the proxy file, but just the one that shipped with our CRM). Gary Gary when we say the crm sdk we mean the microsoft.crm.proxy.dll which is a .net assembly and also the help file search on msdn.microsoft.com and you will find more about it check out this http://msdn.microsoft.c...

How can I count the number of emails sent externally a month
My boss wants me to find out what our average number of external emails sent per month is. How can I extract this information out of Exchange. Thanks You could enable message tracking, and then you could use Log Parser to get a nice little csv file that you can import in a SQL or Access database file... More info on Log Parse here: http://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx HTH Ilse <test2005@bgop.org.uk> wrote in message news:1134047646.645570.287390@g49g2000cwa.googlegroups.com... > My boss wants me to find out what our average number of exte...

Easy Frequency Counts?
I have a spreadsheet column with about 4,000 zipcodes extracted from a mailing list. Many of the zipcodes are multiples. I would like to run some sort of function to tell me how many instances of each zipcode there are. I've looked at the "frequency" and "histogram" functions, but they seem to require buckets or ranges. I need to know the count for each zipcode even if it is listed only once. Is there an easy way to do this? Many thanks. You could add titles, sort by zipcode, then apply data|subtotals to count each zipcode. Or you could add titles and use Da...

cancel 04-27-10
I DO NOT WISH TO USE YOU AS MY E MAIL ACCESS I WOULD LIKE YOU TO DISCONNECT HE EMAIL FROM MY SYSTEM. I USE WINDOWS MAIL this is a forum for Access Databases..... On Tue, 27 Apr 2010 11:23:42 -0400, "Connie and Bill" <bcrugh@bellsouth.net> wrote: >I DO NOT WISH TO USE YOU AS MY E MAIL ACCESS I WOULD LIKE YOU TO DISCONNECT >HE EMAIL FROM MY SYSTEM. I USE WINDOWS MAIL This is not email, Connie and Bill. Windows Mail has two functions - "Mail" and "News". You can use it for either. If you do not want to see the Access newsgroup mes...

Sorting to count
Reason Location BENT 1 POR 2 BENT 2 POR 1 BENT 1 I want to set a formula that will count only the location 1's that ar bent. Can anyone help?????????????????????:confused: ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com YOu can put in a new column with the following =if(and(a1=&...

SumProduct counting negative numbers
I have 2 columns as follows: A B -$24.00 n/a $200 n/a $350 n/a $65 9.30% $32 n/a n/a 5% i need a function that will count the number of times column A appears with n/a only ( so in this case its 4). I have this so far...i don't know how to write it so its counts the negative value: =SUMPRODUCT(--(G5:G15>=0),--(H5:H15="n/a")) I also need a formula that will count the occurrance of when column B has a value when column A has an n/a. In this case the answer would be 1. I hope that make sense... Correction: i need a functio...

Problem with FORM 05-11-07
I have created a single FORM with lots of fields that contains records. The problem I have, I needed to create lots of box of fields over several pages for a singe customer information. I assume your form contains ONE RECORD, not RECORDS as your post indicates. If I understand, you would like to break the various fields up into smaller logical chunks? If I were you, I would add a tab control (it is one of the standard controls) and then put the various controls on two or more tabs. I've found that it looks nice to have the basic data at the top of the screen (on the main form)...

Email Template in Campaign Activity not available 04-24-06
If I create a Campaign in CRM and decide to communicate via Email with my marketing list members by using a Campaign Activity with "Email" as channel, I dont seem to be able to personalize the activity? One would expect to be able to leverage from Email Templates from within Campaigns...comments? ...