Help manipulating data please

I used to know what I was doing with Access but I'm really rusty and can't 
find a way to solve this problem, so any help would be much aprreciated...

I've simplified things for this posting, so let's say I've got 10 records 
and 5 fields (in practice there are a lot more but the principles will be the 
same).  Each record represents one person's answers to five questions.  The 
'answer' in each of the five fields has a numeric value between -42 and +42.

What I need to do is to find out how many people put their lowest answer for 
question 1, then how many people put their lowest answer for question 2 and 
so on.  If a person has put the same lowest, numeric answer for more than one 
question, their response needs to be included in the count-up for each of 
those questions .

I realise I could sort the answers for each person manually, make a note of 
which question or questions they put their lowest value for, then count them 
up.  But I am sure there are lots of bright people out there that can explain 
how to automate the process.  Hope so, anyway...!  ;-)

0
Utf
3/28/2007 12:22:50 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1115 Views

Similar Articles

[PageSpeed] 54

Ok, I am going to take a lot of liberties with this one but this is what I 
think is the fastest way to do it.

First, your data will be better off if it is in a large array table with the 
keys of student id and question #. So each person has as many records in that 
table as the questions that they have answered. The layout of the new table 
is:

studentID long
question integer
answer integer

Now the following sql will get you the min answer per studentid (query named 
minanswer)

SELECT personanswers.personID, Min(personanswers.answer) AS MinAns
FROM personanswers
GROUP BY personanswers.personID;

the following will tell you which question had this min answer (named 
minquestion)

SELECT personanswers.personID, personanswers.question
FROM minanswer INNER JOIN personanswers ON (minanswer.MinAns = 
personanswers.answer) AND (minanswer.personID = personanswers.personID);

and the following query will get you a count of how many were answered per 
question number (although you will not get a list of all questions, just the 
ones with at least 1 marked as lowest)

SELECT minquestion.question, Count(minquestion.personID) AS CountOfpersonID
FROM minquestion
GROUP BY minquestion.question;

Please let me know if this suited your needs, or if I can help you more.

0
Utf
3/28/2007 1:17:48 AM
Reply:

Similar Artilces:

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

help #2
Hi, I need help with the following: I need to plot a 2-D column graph comparing the profit margin (y-axis) for the 3 items (x-axis) for Malaysia and Singapore, using the data below; Malaysia Item Mfr's Agent's Retailer's Selling GST Profit Total Cost Charge Markup Price Margin Lipstick 9.85 15.76 12.81 38.42 1.54 27.03 105.40 Toner 11.90 19.04 15.47 46.41 1.86 32.65 127.33 Cleanser 15.90 25.44 20.67 62.01 2.48 43.63 170.13 Total 37.65 60.24 48.95 146.84 5.87 103.31 402.86 Singapore Item Mfr's Agent's Retailer's Selling GST...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

Credit Card Downloading HELP!
I had 2005 and this worked ok but not on 2006. I download my 20 credit card transactions in QIF format. Money says it has 20 to review so I click that and they simply do not appear in the register! The the heck?!? I've tried quite a few things already to no avail. Deleted history and temp, deleted the statement and previous transactions/statements, repaired my money file a few times, tried to import in a different file but it just doesn't show up. Suggestions please...tia In microsoft.public.money, lucas.allen@gmail.com wrote: >I had 2005 and this worked ok but not on 2006....

Errror Help: character ' ', hexadecimal value 0x2 is illegal in xml documents
what does this error mean? how can i fix this? thanks, raj * raj wrote in microsoft.public.dotnet.xml: >what does this error mean? how can i fix this? You have an octet 0x02 or perhaps a character reference &#x02; in the document. This is not allowed in XML 1.0; the latter is allowed in XML 1.1 but your version of the .NET framework probably does not support that, and most likely you have not declared this version either. In other words, your document includes random binary data and is there- fore not an XML document. You have to correct the document before you can successfully...

mail gateway help
we have barracuda for our mail firewall but you can send mail out through it. Where do you normally point your exchange server to send mail out? the internet router the pix firewall ? "=?Utf-8?B?RWRkaWU=?=" <Eddie@discussions.microsoft.com> wrote in news:EF923D10-571B-42D0-85E3-D45CB9FC2A1C@microsoft.com: > we have barracuda for our mail firewall but you can send mail out > through it. Where do you normally point your exchange server to send > mail out? the internet router the pix firewall ? > What exactly is your question? What version of exchange are you us...

Where is "Help" located in 2007?
I cannot find the "Help" section in Word 2007. Where can I find it? Thank you! Click the question mark ? top right of the Word window or press F1 which is the universal help call. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "Todd" <Todd@discussion...

MFC classes DISAPPEAR!!!!help
could someone please help me: many MFC classes almost always disappear from class browser window!!! while their files continue to be properly listed in the file browser window thanks! Bianca Try deleting the clw file and let the class wizard recreate it for you. If that doesnt help also try deleting the opt and ncb files. Or else try upgrading to Everett :-) -- Regards, Nish [VC++ MVP] "BIANCA" <bbb_1973@virgilio.it> wrote in message news:d2504dc9.0308140037.699a9ae8@posting.google.com... > could someone please help me: > many MFC classes almost always disappear from...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

VLookUp Help!
I am trying to put together a pricing wizard for my company that would allow reps and resellers to easily look up the prices of our products. I originally designed a pivot table with drop down lists but this proved to be to difficult for most people to use. I though instead I could use VLookup to accomplish the same thing. The problem is we price our products by quantity. 5 to 25 being one price 26 to 50 being another and so on. Because of the ranges of pricing I can not figure a way, using VLookup to actually look up a price. Here is an example of the pricing: # Part # of Licen...