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
1088 Views

Similar Articles

[PageSpeed] 56

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:

using common control version 6 in VC6
Hi all, I'm trying to use the common control version 6 in my MFC project, I've imported the manifest.xml adn specified 6 as the platform, but I still can't use the version 6 members of the NMLVCUSTOMDRAW structure in my custom drawn controls. for instance, when I tried to use clrFace of the structure, it would say 'clrFace' : is not a member of 'tagNMLVCUSTOMDRAW', and I've traced the problem to the definition of the strucfure itself, typedef struct tagNMLVCUSTOMDRAW { NMCUSTOMDRAW nmcd; COLORREF clrText; COLORREF clrTextBk; #if (_WIN32_IE...

Item record in RM00101
Does anyone know how an item record is entered into RM00101? I only have one in there and I can't figure out how it got there. Thanks. Chris, Do you mean there is an inventory item in there? If so, most likely there was either a data entry error or an import direct to table error. If that's not what you mean, can you please elaborate? -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Chris" <Chris@discussions.microsoft.com> wrote in message news:AEA12C29-07...

Birthday and Anniversary Records
I am new to Access and am responbsible for mailing out birthday and anniversary cards for our company. I have a table with employee names, address, DOH and DOB. I want to keep record of the date I mailed these cards out. Would I create another table to record those dates? Would that table include the employee name, card type and mailing date? If I enter the mailing date in a form, how does the form record the date in the table with that table? Yes, another table is required. I hope you have an EmployeeID in the employee table, because you shouldn't store employee name in two pl...

Return to original record
I have 2 subforms on a main form. I leave subform_01 and go to subform_02, enter information there. Then, at the click of a button on subform_02, I want to go back to the exact record I was on in subform_01 before I left subform_01. Any ideas? Thank you, -- RJF Are the two subforms both open at the same time, or does subform 1 close when you to to subform 2? If the form stays open then all you should have to do is set the focus back to a control on subform1. The code for that would look something like the following (note that subform1ControlName is the name of the subform CONT...

SQL View duplicating records
I've created a view on inventory transactions linking IV30300 and IV30400, wanting to pull the lot number information into the view for each inventory trx. I have a transaction that was an INVADJ, with one item on it, recorded at 3 different quantities, 3 different costs, and 3 different lot numbers. In my results pane, this transaction is listed 9 times, once for each combination of qty, cost, and lot number. What causes this to happen and how do I change my query to pull only one line per combination of qty, cost and lot number in this transaction? Thanks for the help. Frank Hamelly...

Control Tab for Combo box
Hi, I have created a combo box in Excel 2002. When I right click and select Format Control the Control tab is missing. Any ideas about how to fix this? You created a combobox using the controltoolbox toolbar. The "control tab" doesn't exist for this combobox. But if you use a dropdown (aka combobox) from the Forms toolbar, you'll be one happy camper again. Ruth wrote: > > Hi, > > I have created a combo box in Excel 2002. > When I right click and select Format Control the Control > tab is missing. > > Any ideas about how to fix this? -- D...

How to disable the "implicit mx record" in Exchange
I am having a problem with exchange sending to hosts in recipient domains where these hosts are not actually mail servers. After a lot of review of the SMTP logs, I realized that sometimes Exchange is sending to the correct MX record host, and sometimes it is sending to the host with the A record for the actual domain. When I say the record for the domain, I mean an A record that refences the bare domain name rather than an individual host in the domain. So when Exchange gets a DNS timeout looking up an MX record, it falls back to sending to the domain A record. This causes an immed...

Units of Measure in Edit Controls
Is there a good way for me to format data in an edit control by that control's unit of measure? For example: CString data = foo; FormatUnits(data, IDC_VALUE_CTL); c_value.SetWindowText(data); In FormatUnits(), of course, I have a basic problem: how do I determine how to format "data" by knowing my control ID? In the COM world, I'm used to creating and storing a custom property that I can access later. Is there a way to do this with MFC? Or is there a "best practices" way of doing this and I'm barking up the wrong tree? Seeing how no one has replied yet,* ...

records before 6 months
Hello everyone, Anyone know how to select records before 6 months? Basically I have a date field and I need to select records older than 6 months from the current date. So today is 03/29/10. I would need all the records BEFORE 10/29/09. Let me know what you guys think. Thanks, Himansu On Mar 29, 2:24=A0pm, "Himansu" <himansu...@hotmail.com> wrote: > Hello everyone, > > Anyone know how to select records before 6 months? > Basically I have a date field and I need to select records > older than 6 months from the current date. =A0So today is...

Docking Multiple Control Bars on the same side
Hello, I am trying to initiate my application custom made ControlBars docked on the left of the frame but I am not getting the right result: To illustrate what I want to do picture the Microsoft Visual Studio enviroment. Normally you start with a few Control bars docked to the side of the frame. Mine for instance has the "Class View" and then just below the "Properties Window" , they are both docked to the same side one above the other. My problem is iamigne if when you started your MS Visual Studio instead of having both controlBars one above the other, you would...

correct formulation of expression in control source
How can I combine 2 expressions that use the DCount function? For example, in control source, =DCount("[Field1]","qryX","[Field1]=True") will give me a value Y which I need to add to another value obtained by =DCount("[Field1]",qryZ","[Field1]=True"). I have tried various bits such as =DCount("[Field1]","qryX" And "qryZ","[Field1]=True") but that does not work. Can anyone help? Regards Have you tried =DCount("[Field1]","qryX","[Field1]=True") + DCount("[Field1...

Record Locking 03-17-10
For a Ms Access 2003 Db, Split, multi-user (10 users or so) in a peer-2-peer environment. I read that it can be beneficial to remove record locking on the forms and am confused?! I thought edited record was what should be setup. Why not? If you remove it, then how are updates managed in the rare even 2 user work on the same record? Thank you for the clarifications. QB "QB" <QB@discussions.microsoft.com> wrote in message news:65FEF730-D717-41C8-8ECA-C0D3A7A1B8DE@microsoft.com... > For a Ms Access 2003 Db, Split, multi-user (10 users or so) in a &g...

Cube View required to count Milestones over time
I have a basic Data Analysis view which counts milestones over time..however I now what to count a breakdown of those milestones over time..I have Milestone types 1,2 and 3. I have created a Milestone Type custom field at task level to set a value against each milestone ie 1, 2 or 3. How do I then get this Milestone Type field into the Cubes in order to add it to the view? Which cube should I be using etc?? ...

Changing a control on a subform?
I am trying to turn some controls in a subform on or off by using a check box on the main form, can�t get it to work Here is the code on the check box click event Private Sub ckShowAWIPrice_Click() If Me.ckShowAWIPrice = -1 Then Me!frmDataSubform.Form!txtStandardBond.Visible = True Me!frmDataSubform.Form!txtStandardNet.Visible = False End If End Sub i'm guessing that "frmDataSubform" is the name of the form (that you're using as a subform) as it shows in the database window. is it also the name of the *subform control within the main form*? your expression has ...

Identify Duplicates (Group Records First)
Hello, I have an invoice table that includes the fields CustomerNumber and DivisionNumber. Customers can deal with multiple divisions. CustomerNumber DivisionNumber ABC Co 10 ABC Co 10 ABC Co 10 Smith Inc 10 Smith Inc 10 Jones Corp 20 Jones Corp 20 Jones Corp 10 XYZ Inc 20 XYZ Inc 20 I am trying to come up with a query that will identify that Jones Corp has records in both division 20 and 10. I would like the results ...

Can't edit publisher_address_list after appending records with Acc
With Office 2000 if I create a publisher_address_list and then use Access to append records to it, I cannot use the Edit publisher_address_list feature in Publisher to edit or add records. Is there a setting I need to change? ...

COUNT(DISTINCT xx) with OVER PARTITION
I have a table of Visits and wanted to return a subset of all visits in the table along with the distinct count of clients per employee within that subset of rows. I can do it with a subquery or maybe a CTE, but the actual (production) query has a lot of parameters so it really complicates it. I was hoping to use the OVER PARTITION for this, but it errors when I use the DISTINCT on the COUNT. I don't see anything in BOL mentioned about this not being supported. Am I doing something wrong or is this simply something not supported? Using SQL 2008 SP1 CREATE TABLE Visit ...

record multiple invoices to a single deposit
Hello, I was trying to find a way to post several invoices to a single deposit. Is this possible? For example, I receive payment for a $25 and a payment for $45 I will make a deposit to the bank for $70, but in my money register it shows as a $25 and $45 deposit on the same day. I would like for the money register to match the actual deposit amount. Thank you in advance. Here's a link to a previous thread on this issue: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=%23ASSakB2CHA.2644%40TK2MSFTNGP11.phx.gbl&rnum=1&prev=/groups%3Fq%3D...

count matching numbers
This is probably simple but I'm about to go crazy trying to figure it out. Please help. I'm trying to count or total how many cells match another row of cells. For instance A4 = 23 B4 = 14 A5 = 34 B5 = 30 A6 = 39 B6 = 34 A7 = 48 B7 = 39 A8 = 53 B8 = 55 The answer I'm looking for is 2 because there are two cells that match. (A5 matches B6 and A6 matches B7) Thanks, mike Try ths: =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A8,B4:B8,0)))) -- Biff Microsoft Excel MVP "Catfish" <mtblanton@gmail.com> wrote in message news:491279e0-18f9-4d29-a...

Security gap
We have found a security gap after modifying, creating, or copying roles. Scenario: User1 has "user" privileges to create, write and read, and assign accounts. User1 creates account and then assigns account to User2. User1 can still read, write, ect to the account. Has anyone seen similar security flaws? I know exactly what you are talking about. I import the leads for my company and then assign them to the appropriate sales person. However, if I go to "active leads" I can still open them and modify them. Maybe this is something that will be fixed soon. JSF2004 ...

Security problem with controls in worksheet
I've built a worksheet that contains a few controls (check box). I need to protect this worksheet, but maintain access to these check boxes. I've set the security to unlock on the cell in back of my control and also my control is unlocked but I always get and error saying that my worksheet is locked when I check or uncheck my control. Can someone help me out on this one! Thanks alot. I'm afraid all you can do with the Forms' check boxes is in the Format Control. I don't know of anyway to change the font size or color of the text. For more features you will have to ...

No new records
Hello. I have a subform in a form that I use to add records. Is there a way to keep this subform from sorting through all the records in the query it pulls from? I have the query use a query with an autonumber and have set it to Allow Adds:No. Not sure why it still scrolls through records. Thanks <nybaseball22@gmail.com> wrote in message news:5e96e569-0322-4c34-89c2-dec1edb75f5d@z17g2000hsg.googlegroups.com... > Hello. I have a subform in a form that I use to add records. Is > there a way to keep this subform from sorting through all the records > in the query it pulls ...

vc6 using SSTab control
Hi, Is there a way to use this control so that each tab holds a variety of buttons,labels,etc? When I add them similarly to adding in VB, they appear when project is first run but not after leaving/returning from/to the tab where I thought (!) that I placed them. thanks, J No. Control Containment such as this is a VB concept and relies on the control actually running at design time so that it can be hidden / shown... The closest you will get to this is by using Dialog resources with controls within them. Dialogs can be used as Child windows so their visibility can be switched at run t...

Counting Cells which are shaded a certain colour
Hello, I have a spreadsheet set up as a year calender for booking annual leave at work. Each staff member shades cells their own colour to book annual leave. I'd like to set up a function which counts the total number of cells shaded a certain colour - so when someone adds leave, their own personal running total is displayed. Hence I need a formula which counts the total number of cells in the worksheet which are a certain colour (using the standard palette). I'd be very grateful if anyone could let me know if there's a simple way to do this? TIA for any help Paul Paul, have...

Access crashes / freezes on new record
For no apparent reason, something strange is happening to my DB. When I add a new record using a form, Access crashes and I am forced to kill it with the task manager. Interestingly this doesn't happen when I add a new record 'directly' into the table, nor, if after only filling in one field in this way, I then return to the entry form and fill in the rest of the fields. The form has a subform within it. More specifically, the form enters details into an 'Invoice' table and the subform enters details into an 'Items' table. The 'Invoice number' is what conne...