Count uniq records and assign to a control

SID is the field of underline query of a form (have many duplicates). I want 
to count number of uniq SID and assign the result to TotalStudent control on 
the form. Please help. Thanks

- Song 


0
Song
3/23/2007 1:09:44 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1034 Views

Similar Articles

[PageSpeed] 12

In article <e90AFyUbHHA.1388@TK2MSFTNGP05.phx.gbl>, 
csitnnn@hotmail.com says...
> SID is the field of underline query of a form (have many duplicates). I want 
> to count number of uniq SID and assign the result to TotalStudent control on 
> the form. Please help. Thanks
> 
> - Song 
> 
> 
> 
Maybe with a saved query such as
Query1: SELECT DISTINCT SID FROM UnderlyingQuery;

=DCOUNT("SID","Query1")

0
Michael
3/23/2007 8:47:59 PM
Song Su wrote:

>SID is the field of underline query of a form (have many duplicates). I want 
>to count number of uniq SID and assign the result to TotalStudent control on 
>the form. Please help. Thanks


You need a separate query to calculate a unique count.

query:  DistinctCount
SELECT Count(*) As SIDcount
FROM (SELECT DISTINCT SID FROM formquery)

Then the TotalStudent text box can use the exression:
	=DLookup("SIDcount", "DistinctCount")

-- 
Marsh
MVP [MS Access]
0
Marshall
3/23/2007 10:24:32 PM
Reply:

Similar Artilces:

count distinct in Pivot table
the data is alike A B C A when I count, I want to count how many distinct items, the result of above data shouls show only 3 (A, B and C). when I use pivot, the total shows 4 (counting 2 As). Pls help how to get this done. A pivot table won't calculate a unique count. You could add a column to the list, then add that field to the pivot table. For example, to count unique items in column A, use the following formula: =IF(COUNTIF($A$1:A2,A2)=1,1,0) Copy this formula down to all rows in the list, then add the field to the pivot table. soe wrote: > the data is alike > A > ...

Assign a hot key
Is it possible to assign hot keys in Outlook 2007? I want to assign hot keys to auto strike text and other common functions that currently don't have one assigned. I've been searching the web but can't find an answer, anyone know? Thanks You can add the Strikethrough command to the Quick Access Toolbar and then use the ALT+# keyboard shortcut. See http://www.msoutlook.info/question/170 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.in...

Cannot add records to subform
I have a form with a subform (linked by drag and drop). The main form has a "Add New Record" button. When I add a new record via the button, I can enter info in the main form, but not in the subform. How can I fix this? Thanks. Since the button is on the main form, the new record goes to the record source of the main form. To go to a new record on the subform, either move the 'Add New Record' button to the subform, or programmatically (VBA) change the add new record reference to work on the subform instead of the main form. "tc2004" wrote: ...

Recording 457 plans in Money
Anyone have an idea how I should characterize a 457 plan in Money 2005? Do you use the 401K feature or is there a better way. Lastly, how do you take care of "units" of a stock or mutual funds? My statement does not indicate shares of stock/MF specifically...it only lists units at such a cost which doesn't mirror the actually cost per share of a stock or mutual fund...thanks. I wouldn't use the 401k type. Probably a regular investment account. This will have Tax Estimator complications. Flagging it as a Retirement account would have Lifetime Planner complications. Pic...

Recording elapsed time
What formula do I use for elapsed time so I can calculate calories/hour from my exercise routine, for example? The routines vary in lengthI can calculate average time and average calories but is there a way I can use the hour:minute:second format (or some other elapsed time format) to divide the calories by, or do I have to convert all times to hours, for example, and proceed that way? In what format do I enter the time data? I am using Office XP Professional with Publisher Version 2002. Hi Kap, Perhaps this would be ok ? If times are entered in 24 hour format with hours and minutes ...

Updating a list control dynamically per group?
Hi there, This is driving me nuts. I've got a form, which is based on a query. Each record is identified by an ID. I have grouped by ID, and have a header/footer for each different iteration of ID. Pretty simple. BUT. I want to dynamically access some other data using a list control, based on the ID. As the ID changes for every iteration (obviously), I want the data to reflect the ID of that Iteration. However, it would seam it doesn't perform a requery for each iteration of the ID, rather just performing the query for the first iteration, then using this for every other. I...

Filter records
Hi,I have a form that lists transactions. I would like to limit the listto show transactions from the previous 14 days.TIA Base the form on a query. Set the criteria for the date field to:>DateAdd("d",-14,Date())See Help for more information about the DateAdd function.<myxmaster@hotmail.com> wrote in message news:1173110246.288701.261950@t69g2000cwt.googlegroups.com...> Hi,> I have a form that lists transactions. I would like to limit the list> to show transactions from the previous 14 days.>> TIA> ...

Total Records in CRM View
Hi, i have to display in crm view footer like 1 from 250 out of 1000 thanks in advance !!!!!! Hi Mahain, I am sorry that you can't do that in CRM currently. You need to write something custom to accomplish this task. Darren Liu, Microsoft CRM MVP Crowe Horwath http://www.crowecrm.com On Jan 14, 12:27=A0am, Mahain <mahende...@gmail.com> wrote: > Hi, > > i have to display in crm view footer like > > 1 from 250 out of 1000 > > thanks in advance !!!!!! ...

Tab Control Subform SetFocus
My main form has a tab control which has a subform embedded in datasheet view. The main form (frmNewProd) has a combo box (cboShift). I want to make a selection in the combo box, hit <enter> and set the focus to the first field (WorkstationID) of the first record in the empty subform (sfmProdOp) which is embedded in the tab control (tabProdDetails). I have tried attaching the following code to the AfterUpdate event of the combo box on the main form: Private Sub cboShift_AfterUpdate() Me!sfmProdOp.SetFocus Me!sfmProdOp.Form!WorkstationID.SetFocus End Sub but it doesn't go ...

How do I set up a record keeping on Water Meters
I work for a utility company that rebuilds water meters and I need to have a program that stores and puts in order the water meters that we have in the system & that are due to be tested, & what year they are to be tested. Just sort on that key data. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Arvin" <Arvin@discussions.microsoft.com> wrote in message news:58BDE61B-CAA5-468D-8560-281966E8B992@microsoft.com... > I work for a utility company that rebuilds water meters and I need to have a > program that stores and puts in order...

Error on first record of integration
I am always getting the following error on the first record of each integration (I run a group, which contains 2 separate integrations): Could not set foreground locking value to 0 I found the knowledge article which says to increase the time between the macro to launch GP (scheduled task) and the Run Integration Group (scheduled task), so I did (set to 5 minutes instead of 2). It only takes 30 seconds total to completely log into the company. However, the error still occurs on both integrations. Why would the first record on the second integration fail as well if it was a timing i...

Controlling drawing objects in Excel . . .
i m trying to do the following. 1) i draw 10 circles, 10 rectangles and 10 ellipses (all are scattering here & there) in excel. 2) i would like to change all the sizes of circles with minimum steps. [Is it possible to control in such a way that a circle is drawn as a master one, and that circle will be copied and copied until required number. When we want to change something to all circles, we will change the master circle, and all other circles will be automatically changed.] If someone knows, kindly explain to me. Thanks & Have a nice day!!! Thaw Htin Oo. Thaw, (Oo?) One...

Exchange 5.5 : anyone remember how to specify the exact domain controller to use???
Hello, We still have a client using a single Exchange 5.5 server for a specific application. They're going to replace it but not until next year. In the meantime, I need to force it to use one specific domain controller because the others have been security hardened. Does anyone remember please how to tell Ex 5.5 which DC to use all the time? Thanks, - Alan. You might try separating the Exchange server and specific domain controller into their own AD site. -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. &quo...

I would like to control spam
Is there anyway to start the spam that I get . I receive tons of it each day. I keep on placing the addresses in the junk mail option in ms outlook and more keep on happening each day. I don't know what you mean by "start," but we use a company called Atlanta Internet Consulting. They have great anti-spam software. Works great and is inexpensive. Our contact there is Reggie. Their phone number is 678.947.8139. Oh, and we use it in conjunction with Outlook. -Charlotte Crenshaw >-----Original Message----- >Is there anyway to start the spam that I get . I rece...

Incorrect record length #2
Hi all, I'm using GP 9.0 and i'm getting this error when trying to run PURCHASING reports. "An open operation on table 'TX_Detail_Period_SUM_TRX' has an incorrect record length." I tried to find this table in the database but couldn't find it. I searched the Knowledge base for this error but didn't find any article related to this particular table. Any insight? thank you, Eduardo This table is a temp table that is created in your local temp folder (not sql). The problem is that you have old temp files laying around and now when GP tries to make...

query to locate which record was not added
Hello, will try to explain. i have 4 employees. jim, jack, joe, and john. every week a report is created that needs to have one record from each of them. But they are not responsible and may forget to submit data. first week Table1 name endDate Jim 2/6/10 Joe 2/6/10 John 2/6/10 * Jack forgot to turn in his report* Is there anyway to find this out with a query? sql statement? you may say -you can see easy with 4 records - who is missing- but with 50 names and if it was 49 entries it will take longer to find name not there. I am able to count the records and display on rep...

how to sum and count in subtotal
How do i apply or use from sub-total one column as sum and another colum as count using the same data: Example: Amount Store 1 $500 Store 2 300.00 subtotal $800 Using sum is $800 but using Count should be 2 One kind of cheating way is to just add another column: =if(b2="",0,1) Then include that field in your subtotal (as sum). Tina wrote: > > How do i apply or use from sub-total one column as sum and another colum as > count using the same data: Example: > ...

Associate an activity to each record of a marketing list
Hi all, If Im not wrong, when distibuting a campaign activity, that activity is assigned to people (record owers or myself). However, I would like the activity to be displayed in the activities of each record of the marketing list. How to do that? Thank you! ...

How to record Fed & St Paycheck Deductions
I would like to keep track of my Federal and State Paycheck deductions and be able to draw up reports on them through money. However I have my paycheck direct deposited so only the net is ever deposited in my checking account. I use direct update to input my net paycheck into Money. Since my Federal and State Paycheck deductions are never entered into Money I cannot categorize them. How do I input my Federal and State paycheck deductions into Money 2006? Search help for 'paycheck'. Click the link to 'Edit a net paycheck to reflect deductions'. "Scott McNabb"...

Printing content in a record
Hello everyone... Happy new year! I have MS Outlook 2003 that came with Office 2003 Professional. with service pack 3 running on XP Home edition, SP2 I want to print all my contacts from within a contact folder, but I want to include the notes that I keep within each record. These are the manually entered notes that are kept under the web page address and the IM address on the default contacts form in which you enter contact information. Currently, when the records are printed, the contact name and address and email address and phone numbers are printing, but the notes are being left behi...

No "Show Profiles" in "Control Panel"
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi all, sometime I see help pages, like the Microsoft Knowledge Base Articles, that say something like: Click Start, point to Settings, and then click Control Panel. Double-click the Mail icon. In the Mail Setup dialog box, click Show Profiles. The problem is that in the "Mail Setup" I have no "Show Profiles". Currently I have no need to apply any help page that indicate this feature, but I had many cases in the past (and so I assume I will in the future) and so I would like to solve this issues in advance. Thank in advance....

Counting #4
I have an excel spreadsheet which I am developing as a record of hills I have climbed. The hills are broken down into geographic areas and the list can be sorted by a variety of criteria (height, area, distance from a given point, etc). Each hill has a unique reference and the first 4 characters identify the area. Each time a hill is climbed, a date is entered in a column and the count function is used in a summary area to show how many hills have been climbed in a given area. I have given this to some others in my club and they have pointed out that if the spreadsheet is sorted by any...

Inventory control problem
I'm a first timer using xls for inventory control but ran into problem. I have 2 columns Pulled and Returns. When you first inpu numbers in it calculates the Inventory on Hand correctly. The proble is that the inputs are done daily and when delete the old numbers i the 2 columns and input the new ones...it disregards the previous day numbers. Is there a formula or function that I can use that would giv me a running total on those two columns. Any suggestions would b greatly apprec. Thanks......Bunj -- Bunj ----------------------------------------------------------------------- Bunji'...

Counting #2
Is it possible to count the number of unique dates in a spicifi column? Ex. A column could contain 6- 4/18/04 dates, 2- 4/30/04 date and 4- 5/10/04 dates the answer would be -- Message posted from http://www.ExcelForum.com One way =SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100))) entered with ctrl + shift & enter -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Karl >" <<Karl.17jn9l@excelforum-nospam.com> wrote in message news:Karl.17jn9l@excelforum-nospam.com... > Is it possible to count the numb...

Disabling buttons on a spinner control
Hi, Lets say you have a edit field that ranges from 1-1000 with an attached spinner. Is it possible to disable the "up" button if you have reached 1000, and the "down" button if the value has reached 1? Many thanks, Alain "Alain Dekker" <abdekker@NOSPAM.fsmail.net> wrote in message news:uN25Z8NtDHA.3196@TK2MSFTNGP11.phx.gbl... > Hi, > > Lets say you have a edit field that ranges from 1-1000 with an attached > spinner. Is it possible to disable the "up" button if you have reached 1000, > and the "down" button if the val...