Counting # of values in a group registration

I have a small DB that keeps track of community events, where people can 
enroll and attend various courses. Each course is entered into the DB as well 
as each enrolled individual, with name, age, etc, but most importantly, the 
individual's Province. For example, Course DF102 - Gardening, has 20 people 
enrolled, with each person having the same or different province (BC, AB, 
ONT, MB, etc).

What I would like to do is be able to generate a query that lists each 
course, and within each course, identify (count) the number of individuals 
from BC, the # from AB, etc, etc, in order to break it down for statistical 
purposes.

Example: DF102, Gardening, Count (BC) = 10, Count (AB) = 8, Count (SK) = 2
 
Thanks,
J.L
0
Utf
1/7/2010 7:57:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
648 Views

Similar Articles

[PageSpeed] 32

create a new query, and enable the "Totals" option (you can right
click in the query and choose this).  Link your Course and Student
tables (or however you have it set up).

Choose Course, Province, and StudentID (or whatever) for your fields.
The "totals" option for both Course and Province should be set to
"Group By".  The "totals" option for StudentID should be set "Count".

This will return the information you are looking for, aye.

0
ghetto_banjo
1/7/2010 8:19:18 PM
Reply:

Similar Artilces:

Chart two variables with differing values
I'd like to chart data similar to: Year 2000 2001 2002 2003 2004 Thefts 75 120 95 101 80 Veh. Mfg. 100,200 125000 130000 145000 125000 I want to produce a chart with the year on the abcissa and with the Thefts scale on left ordinate and Veh. Mfg on the right ordinate and have the scales set accordingly. I'm running Excel 2002. Seems i can't select the Format/Selected Series data Series/Secondary Axis to set the scales correctly. I'd like to get som...

If Count is not in a certain range, delete Rows
I have a spreadsheet that is 59752 x 2, with the one column Contact ID A and the other column Contact Link ID. It is like an instant messaging system. It looks something like this: 1 2 1 3 1 15 2 1 2 15 I want to count by different values in column A, and any count that is not within one of several ranges, those rows get deleted. So if the range is 3-5 contacts, then I want to keep the set of 1s, but delete the set of 2s. My ranges are 4-5 contacts, 30-41 contacts, and 100+ contacts. I am also brand new to macros, but I have experience with some other coding ...

How to pass value from one form to another form.
I have the following code in BookList.aspx: <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="LibSysDataSource" AllowPaging="True"> <Columns> <asp:BoundField DataField="AccessionNo" HeaderText="Accession No" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title&qu...

Change Admin Group
Is anyone aware of a workaround for changing a server from one Administrative Group to another? I know they aren't easily moved looking for a hack if available. Many thanks, Jeff "Jeff Abbott" <jeff.abbott@veritas.com> wrote: >Is anyone aware of a workaround for changing a server from one >Administrative Group to another? I know they aren't easily moved looking >for a hack if available. Moving the server's easy. Making it work after you do it's a bitch. Moving an object between containers with ADSI isn't hard. Getting all the objects renam...

CSocket.Connect() Port Value
All: What is the best way to determine the "Host Port" value in the CSocket.Connect() function? The examples use the value 700 or greater. Can there be a conflict if I just use the value 700? -- Regards; Bruce Kingsley The correct port value to use is the port number of the port you are trying to connect to. That is determined by your host. If you are writing the host, unless you are implementing a standard protocol, it should not be < 1024. It must not be hardwired unless you have a registered port number (which you must get from IANA, the Internet Assigned Number Autho...

Field values used to filter a report
I have a report that is based upon a query that filters the data in a variety of ways. The net result is a listing of Zip Codes that I ultimately use as a part of the data for a report. The contents of this report is limited to the Zip Codes from the query. (Not a true filter) I need to be able to print this list of zip codes in a comma seperated list in the report header. I am sure this can be easily accomplished but for the life of me I cannot figure this out. Any help would be greatly appreciated. Thanks in advance. There are 2 parts to your question: a) How to provide an interface ...

In Project 2007, Group By Baseline Start is Rolling Up Max, not Mi
In Project 2007, when you display the Baseline Start field in view with grouping turned on, Baseline Start displays the Maximum Baseline Start of the tasks that form the group, not the minimum. To work around this bug, customize a Start or Date field to equal Baseline Start with Summary calculating rollup minimum. ...

display last value in a column
I would like to display the last value unequal to zero recorded in a column in a specific cell. For example, A1 is to display the last value unequal to zero recorded in column B. The number of entries and values in column B changes frequently; therefore the last value in column B is in a different row. Thanks. -- Mike Try this... Assuming there are no logical TRUE entries in the range. =LOOKUP(1E100,1/B2:B100,B2:B100) -- Biff Microsoft Excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:DFFCBDB0-3632-46AC-A3AB-073E6F0D475...

Values
I would like to add 2 values (value and percent) to my column bar chart. I need to see my raw N and the percent on one bar. Anyone know how to do that? As an example, set your data up as follows assuming the below: (1) The letters are in column A (2) The numbers are in column B (3) The percentages are in column C a 4 25% b 3 19% c 4 25% d 5 31% Create a Clustered Column Chart. Double-click on the columns and in the Format Data Series dialog box go to the Data Labels tab and check “Label contains Value”. When complete, you should have data labels for all of the bars. Again, in the...

Count rows based on multiple criteria
I have a need to count the number of rows in an Excel spreadsheet based on contents of multiple cells. The formula is on one worksheet and all data is on a second worksheet. IE: count all rows that have the word "Windows" in column B and the word "Complete" in column G. Have not been able to figure this out with common functions like count, countif, dcount, etc. Help appreciated. -- DMG IT Professional Search for SUMPRODUCT or SUMIFS if you use XL-2007 "dmg" wrote: > I have a need to count the number of rows in an Excel spreadshee...

Cancel button's ActiveControl reflects as value of text box
Hi All, On my form I have an "on exit" code to test whether its text box is "empty" or not. When I click the "Cancel" button I still get the msg that the txt box is empty. Using Debug.print to show the Me.ActiveControl gives me the value of the preceeding txt box. What I'm trying to achieve is to ignore the "empty" test if cancel has been selected by exiting the "on exit" before the msg box sub & clearly I'm misunderstanding the usage here somewhere. Hugh I'm a bit unclear what you want to accomplish... ...

Counting within a group
Hi, In my Summary report, there are two groups: jobNo within Category. jobNo contains data which can be repetative, but not for all records. In other words, there can be: 6 records with jobNo 1234, 2 records with 4567 and 1 record for 9701. I want to count the number of times that jobNo changes, but not the total number of jobNo records. I tried a query using an expression Max(jobNo), then tried to sum it in the report, but it gives the total number for jobNo's. How can I achieve the result I am seeking? Thanks in advance, Jake If you want to list all the records on the repo...

counting numbers 04-09-10
Hi All, I have a Query like This. Pls Give your Suggestions. DATA: anil 0 sunil 1 anil 1 sunil 0 anil 1 sunil 0 Ans: Anil 2 Sunil 1 I need to count the numbers against name which number is more that 0. Regards, Anil. Maybe this =SUMIF(A1:A6,"Anil",B1:B6) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "showry" wrote: > Hi All, > > I have a Query like This. Pls Give your Suggestions. >...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...

Field name derived from a string value returns a Run-time error 2465.
I have two labels on a form and want to populate them during the run time of my form. The name of the labels are "1" the other is called "2" (without the quotes of course). Field 1 runs OK but two returns a run-time error 2465. Of course, the real form is much bigger. A lot of fields called 1 through 31 are populated from within a loop. To simplify my question I down sized this post to two label fields. Any one know how to solve the problem with label "2"? Any help or hint is appreciated. ' This works OK Me![1].Caption = "abc"...

Counting characters in a range of cells
Is there a way to count to number of times a given charachter occurs in a range of cells? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ David, If it's a worksheet function you want, try =SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"e",""))) which is an array formula, so enter with Ctrl-Shift-Enter. If you want to do it in VBA, use Debug.Print Application.Evaluate("SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,""e"",""...

Access 2007 Query of unique values
I am working with an ODBC connection to an SQL database. In attempting to create a simple query for unique values in a field, I would expect to right click in the quey and request properties. The only information that appears in the property sheeet is the data source name. Is it a problem with the ODBC connection? You need to know what tables and columns are in the database you are connecting to. I dont think Access can tell that since its not within the Access database. -Dorian "Norm" wrote: > I am working with an ODBC connection to an SQL database. In attempting to...

how to fix starting point when counting rows?
is there a way to feed in the the starting cell to begin from when counting the number of rows? i can get the below to work in a macro Dim countRows countRows = Range("C9").End(xlDown).Row but when i try to turn it into a function i cant figure out how to give the starting cell as an argument tks for any help Put "C9" (without the quotes) in cell A1, for example, then you can do this: =3DCOUNT(INDIRECT(A1&":C100")) or =3DCOUNTA(INDIRECT(A1&":C100")) Is that what you mean? Hope this helps. Pete On Aug 29, 4:08=A0pm, polarcap <mem_...@...

Change Font on Character Count
I have a form from the CBOE. The form is used to give responses by students, but has limited space. This worked in Acces 2.0 and Access 97, now it is no longer working! I count the total number of characters on a page. If the count is in certain ranges then I set the font accordingly. Current Code Dim rprt as Report Set rprt = Me With rprt If Me!TextA > 1400 then .fontsize = 13 End If If Me!TextA > 2100 then .fontsize = 12 End I...

Count at Group Level
I have a report, grouped by EmployeeId, I need a count of the number of employees included in the report. In the EmployeeID group header, I have a textbox control, control source =1, runing sum = over group. Sometimes the total is correct, sometimes it is off by 1. Any ideas why this isn't working? Setting the RunningSum property to 'over group' is intended to give an incremental total *within* each group, and would normally be used with a control in the detail section therefore. If, as you have, you use this setting in the case of a control in a group header, as far as...

Setting Up A Query Showing Count Of Fields
Hi, I have a Query which has the following Fields. COMPANY ARRIVAL DEPARTURE In this Query the results displayed are as follows: COMPANY ARRIVAL DEPARTURE TCT 1-5-08 8-5-08 TCT 1-5-08 15-5-08 TCA 2-5-08 8-5-08 PRV 1-5-08 7-5-08 TCA 4-5-08 12-5-08 PRV 5-5-08 10-5-08 and so forth. I want to setup a query where on the top it has the dates as per day and to count as per each company per day. In other words something...

How do I set a pivot table to group quarterly reports by F/Y?
I'm trying to set up a report in Excell using the pivot table function. When I use the "Group Field" function to generate a quarterly report it assigns each quarter according to a calendar year which puts the Jan 2010 data in the 1st QTR and the Oct 2009 data in the 4th Quarter. I want to change this so it begins the 1st QTR in Oct. Is there a way to change the way the pivot table reads and organizes the data? Hi Mark You can aggregate whatever months you want to Quarters manually. Don't use Quarters in the Date Grouping, just use Month Highlight the 3 mon...

LeftMost Populated Value
I would like your help with a formula. In a given row, I want to populate a specific range with the left most value that is not null. Below is an example: Row 1 d e f g h null null 33 45 99 In the C column of that row, I want the formula to produce the value of 33. I want to be able to copy the results down. Thanks for your help One way: =INDEX(D1:G1,MATCH(true,d1:h1<>"",0)) If there's a chance that all 5 cells are empty and you don't want the error...

lookup value & formatting
Hi, Is it possible to undertake a vlookup function or such like which not only imports the value that is found, but also the format of the value, be it a different font or colour instance? Thanks Simon Only by using VBA. I suggest the following :- 1. Select cell with value 2. Find the value in the lookup sheet 3. Copy the cell containing the return value. 3. Go back to the original sheet & Paste (with format) Something like this (you will need to amend as necessary) :- '------------------------------------------------------------------- Sub GET_LOOKUPVALUE() Dim MyVariable ...

Photos for the Group
I hope all the guys will enjoy these pictures. ...