Counting Unique Record #s

I have a database showing assignments with their subassignments.  On reports, 
I am attempting to count unique AssignmentIDs under groups (for 
example different months the assignments are due).  Unfortunately, if the 
assignment has two (or more) sub assignments, the count function counts the 
AssignmentID twice (since it lists the AssignmentID with the SubAssignment 
ID).  Do you know a way to get around this so I can count how many unique 
main assignments are under each group?

0
Utf
1/15/2008 5:50:00 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
862 Views

Similar Articles

[PageSpeed] 1

Totals query.  GroupBy your "group"; Count your AssignmentID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Help4me" <Help4me@discussions.microsoft.com> wrote in message 
news:8977279D-84F2-409F-82AE-4E57D9F0E171@microsoft.com...
>I have a database showing assignments with their subassignments.  On 
>reports,
> I am attempting to count unique AssignmentIDs under groups (for
> example different months the assignments are due).  Unfortunately, if the
> assignment has two (or more) sub assignments, the count function counts 
> the
> AssignmentID twice (since it lists the AssignmentID with the SubAssignment
> ID).  Do you know a way to get around this so I can count how many unique
> main assignments are under each group?
> 


0
Jeff
1/15/2008 5:56:02 PM
Thank you.  I actually had already tried that.  When I build the report, for 
assignments with multiple subassignments, it lists the main assignment twice 
with each subassignment (under the group by category) and therefore counts 
the main assignment twice.

I have Assignment IDs 5 through 13.  AssignmentID 8 has two subassignments 
linked to it.  Can I count unique AssignmentIDs only once?

Thank you so much for your assistance.

"Jeff Boyce" wrote:

> Totals query.  GroupBy your "group"; Count your AssignmentID.
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "Help4me" <Help4me@discussions.microsoft.com> wrote in message 
> news:8977279D-84F2-409F-82AE-4E57D9F0E171@microsoft.com...
> >I have a database showing assignments with their subassignments.  On 
> >reports,
> > I am attempting to count unique AssignmentIDs under groups (for
> > example different months the assignments are due).  Unfortunately, if the
> > assignment has two (or more) sub assignments, the count function counts 
> > the
> > AssignmentID twice (since it lists the AssignmentID with the SubAssignment
> > ID).  Do you know a way to get around this so I can count how many unique
> > main assignments are under each group?
> > 
> 
> 
> 
0
Utf
1/15/2008 7:53:05 PM
Assuming a Group field header and an AssignmentID header, you can place a 
text box in the AssignmentID header:
   Name: txtCountSubs
   Control Source: =1
   Running Sum: Over Group
Then add a text box to the Group Field footer section:
    Control Source: =txtCountSubs
-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"Help4me" wrote:

> Thank you.  I actually had already tried that.  When I build the report, for 
> assignments with multiple subassignments, it lists the main assignment twice 
> with each subassignment (under the group by category) and therefore counts 
> the main assignment twice.
> 
> I have Assignment IDs 5 through 13.  AssignmentID 8 has two subassignments 
> linked to it.  Can I count unique AssignmentIDs only once?
> 
> Thank you so much for your assistance.
> 
> "Jeff Boyce" wrote:
> 
> > Totals query.  GroupBy your "group"; Count your AssignmentID.
> > 
> > Regards
> > 
> > Jeff Boyce
> > Microsoft Office/Access MVP
> > 
> > "Help4me" <Help4me@discussions.microsoft.com> wrote in message 
> > news:8977279D-84F2-409F-82AE-4E57D9F0E171@microsoft.com...
> > >I have a database showing assignments with their subassignments.  On 
> > >reports,
> > > I am attempting to count unique AssignmentIDs under groups (for
> > > example different months the assignments are due).  Unfortunately, if the
> > > assignment has two (or more) sub assignments, the count function counts 
> > > the
> > > AssignmentID twice (since it lists the AssignmentID with the SubAssignment
> > > ID).  Do you know a way to get around this so I can count how many unique
> > > main assignments are under each group?
> > > 
> > 
> > 
> > 
0
Utf
1/15/2008 8:16:04 PM
Thank you, but that does not work, either.  The report lists the main 
assignment (that has two subassignments) twice and therefore counts the 
record twice.

I appreciate your assistance.  I could probably do it if I knew how to write 
code, but I do not.

"Duane Hookom" wrote:

> Assuming a Group field header and an AssignmentID header, you can place a 
> text box in the AssignmentID header:
>    Name: txtCountSubs
>    Control Source: =1
>    Running Sum: Over Group
> Then add a text box to the Group Field footer section:
>     Control Source: =txtCountSubs
> -- 
> Duane Hookom
> Microsoft Access MVP
> If I have helped you, please help me by donating to UCP 
> http://www.access.hookom.net/UCP/Default.htm
> 
> 
> "Help4me" wrote:
> 
> > Thank you.  I actually had already tried that.  When I build the report, for 
> > assignments with multiple subassignments, it lists the main assignment twice 
> > with each subassignment (under the group by category) and therefore counts 
> > the main assignment twice.
> > 
> > I have Assignment IDs 5 through 13.  AssignmentID 8 has two subassignments 
> > linked to it.  Can I count unique AssignmentIDs only once?
> > 
> > Thank you so much for your assistance.
> > 
> > "Jeff Boyce" wrote:
> > 
> > > Totals query.  GroupBy your "group"; Count your AssignmentID.
> > > 
> > > Regards
> > > 
> > > Jeff Boyce
> > > Microsoft Office/Access MVP
> > > 
> > > "Help4me" <Help4me@discussions.microsoft.com> wrote in message 
> > > news:8977279D-84F2-409F-82AE-4E57D9F0E171@microsoft.com...
> > > >I have a database showing assignments with their subassignments.  On 
> > > >reports,
> > > > I am attempting to count unique AssignmentIDs under groups (for
> > > > example different months the assignments are due).  Unfortunately, if the
> > > > assignment has two (or more) sub assignments, the count function counts 
> > > > the
> > > > AssignmentID twice (since it lists the AssignmentID with the SubAssignment
> > > > ID).  Do you know a way to get around this so I can count how many unique
> > > > main assignments are under each group?
> > > > 
> > > 
> > > 
> > > 
0
Utf
1/15/2008 9:55:01 PM
Please disregard my last post.  Everything seems to be counting just fine 
now!  I guess I have all the sorting and fields just the right way!  Thank 
you SO much everyone!

"Duane Hookom" wrote:

> Assuming a Group field header and an AssignmentID header, you can place a 
> text box in the AssignmentID header:
>    Name: txtCountSubs
>    Control Source: =1
>    Running Sum: Over Group
> Then add a text box to the Group Field footer section:
>     Control Source: =txtCountSubs
> -- 
> Duane Hookom
> Microsoft Access MVP
> If I have helped you, please help me by donating to UCP 
> http://www.access.hookom.net/UCP/Default.htm
> 
> 
> "Help4me" wrote:
> 
> > Thank you.  I actually had already tried that.  When I build the report, for 
> > assignments with multiple subassignments, it lists the main assignment twice 
> > with each subassignment (under the group by category) and therefore counts 
> > the main assignment twice.
> > 
> > I have Assignment IDs 5 through 13.  AssignmentID 8 has two subassignments 
> > linked to it.  Can I count unique AssignmentIDs only once?
> > 
> > Thank you so much for your assistance.
> > 
> > "Jeff Boyce" wrote:
> > 
> > > Totals query.  GroupBy your "group"; Count your AssignmentID.
> > > 
> > > Regards
> > > 
> > > Jeff Boyce
> > > Microsoft Office/Access MVP
> > > 
> > > "Help4me" <Help4me@discussions.microsoft.com> wrote in message 
> > > news:8977279D-84F2-409F-82AE-4E57D9F0E171@microsoft.com...
> > > >I have a database showing assignments with their subassignments.  On 
> > > >reports,
> > > > I am attempting to count unique AssignmentIDs under groups (for
> > > > example different months the assignments are due).  Unfortunately, if the
> > > > assignment has two (or more) sub assignments, the count function counts 
> > > > the
> > > > AssignmentID twice (since it lists the AssignmentID with the SubAssignment
> > > > ID).  Do you know a way to get around this so I can count how many unique
> > > > main assignments are under each group?
> > > > 
> > > 
> > > 
> > > 
0
Utf
1/15/2008 11:17:00 PM
Reply:

Similar Artilces:

counting TRUEs and FALSEs
A large range of cells (6 columns by 400 rows) all contain an OR-function formula and all display a TRUE or FALSE value. (Results based on processing of a nearby range of data.) I want to count how many TRUEs occur in each row, and display the result for each row in the 7th column. I'm having trouble figuring this one out, since all the functions I've looked at so far apparently see only the formula and not the value. =countif(a1:f1,TRUE) should work. mitcheroo wrote: > > A large range of cells (6 columns by 400 rows) all contain an > OR-function formula and all display...

delete a record
I am having records just disappear. Is there a way of telling when they were deleted and maybe by whom? Does transaction log give this information? On Fri, 18 Jun 2010 11:54:52 -0700, seeker <seeker@discussions.microsoft.com> wrote: >I am having records just disappear. Is there a way of telling when they were >deleted and maybe by whom? Does transaction log give this information? If it is still in the transaction log then you may be able to use a third party tool to access the information. How do you know it has been deleted? John I have used dbcc log but ...

eula shows every time Word 2003 started
Every time Word (or any of the other Office 2003 applications are started) under WIndows7, I get a dialog box with the EULA, and must accept before it will work (display an image, document, etc.). The S/W has been activated, and I can find no way to prevent the EULA screen from popping up. Once in a while I could live with, but NOT EVERY TIME. Please help - this is extremely annoying. Run the application as administrator in Windows then accept the EULA. Thereafter it should stay away. -- <>>< ><<> ><<> <>>< ><<> ...

How to save a picturebox's image to a .PNG file? (VB6)
Hi all! I have a little program (written in VB6) that saves the image in a picturebox to a BMP file. The problem is that BMP files are several megabytes in size and do not support DPI (dots-per-inch) information for printing. I have turned to the PNG format which provides lossless compression (files are only few kilobytes) and includes DPI information. But how do I save the image in a picturebox to a PNG file? Thanks in advance! "Nando" <hightech@att.net.no.to.sp.am> wrote in message news:uflaC5aMLHA.2100@TK2MSFTNGP06.phx.gbl... > Hi all! I have a lit...

Unread count wrong in folder view
I am having a problem with outlook 2003 where the unread count displayed in the folder view is wrong. I am using IMAP/SSL and they are all subscribed to. I have tried this on two diffrent computer and the account does not show the correct unread count until I clikc on the folder after which it updates instantly to the correct unread count. Any suggestions. I miss email because of this ...

Can I retrieve the key of my record?
Hi I am creating a record using the following code: Set qd = db.QueryDefs("Append Purchase Order") qd.Parameters("pSUPPLIER").Value = [Forms]![Purchase Orders].Form. [FK_SUPPLIER_CODE] qd.Execute dbFailOnError The key field in Purchase Orders is PK_PurchaseOrder. Is there any way I can retrieve the key of the record just created? Stapes Assuming these are Access (JET) tables in Access 2000 or later, try this kind of thing: Function ShowIdentity() As Variant Dim db As DAO.Database Dim rs As DAO.Recordset Set db = DBEngine(0)(0) db.Execute &q...

MIN and MAX for unique fields
[CityName] contains the names of the cities. [ParadeDates] contain the dates of all the events for that city. I need the query to return the MIN of the [ParadeDates] and also the MAX of the [ParadeDates] for each city listed. The MIN and MAX would be in two separate fields. So if Houston is listed 5 times with date ranges from 1/1/2007, 2/1/2007, 3/1/2007, 4/1/2007 and 5/1/2007. The MIN would be 1/1/2007 and the MAX would be 5/1/2007. There will be multiple cities, so then if there is another city Los Angeles, then the earliest and latest date should be shown for that also. Than...

insert record
I'm struggling with something that was pretty easy with WinParadox. Suppose I have three tables; People, Phone, and PhoneType. On my People form, I want a subform in which the user is able to add more phone numbers to the Phone table. I want the user to see two fields; the first field allows a phone number, and the second field allows a 'phone type' lookup from the PhoneType table, that results in placing the PhoneTypeID in the new Phone record. However, all my attempts with the wizards leaves me with a subform in which efforts to add a phone number in the Phone table re...

Forwarding email between 2 isp's
I use Wowway as my isp when I am home in Michigan, but use Comcast at our vacation home in Florida. In the past I have forwarded my email messages automatically from Windows mail (Wowway) to Comcast when I am in Florida and vice-versa....but can't remember now how I did it. Could someone help me forward my messages from Windows mail to my Comcast account? You can probably do it on the Wowway server. Log into the webmail portal and see if you can forward all messages from there. http://portal.wowway.net/ Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download....

Use of old PST file as Outlook2003's default mail delivery
Hi; I copy an Outlook2002 PST file on a machine to an Outlook2003 on another machine and am setting it as Outlook2003's default mail delivery. But, the setting doesn't work. The error message sais that it requires only the Unicode format and request disable its OST file. Does Outlook2003 accept only Unicode PST for its default mail delivery? I am registered and can see myself on author's list. But I can't login and can't create new threads. Why? -- Hajimu Taskahashi You state pst, but then go on to mention ost Are you using Exchange? "Hajimu Taskahashi"...

Filter specific records in subform
My subform tracks when personnel are dispatched, arrive, etc., to different events. My main form tracks the first times for dispatch, enroute, arrive, and the last clearing time. The subform shows the complete history for a specific incident, tracked by a unique sequence number (not an autonumber). The linking field is this sequence number between the main and subform. Currently if a user double-clicks the clearing time box on the main form, it will add the clear event to the subform for the primary unit. I would like to expand this that it detects all other units who also were arr...

Opening a Form On Same Record
What is the syntax to open a single record form from a form that has a list box and has the same record source as the underlying form. The ID field is ContactID. I want to be able to move among the records on the single record form. Thanks, James You should use a combobox instead of a listbox, as the combobox has the AutoExpand feature. This allows you to start typing info into the combobox and it will go to the first record that matches. First you must have your form set up to display the record you want to retrieve, i.e. you must have controls set up with the appropriate Control Sources ...

Update records from one spreadsheet to another
I am trying to update a old spreadsheet with a new spreadsheet. Th original spreadsheet is the property records for the county that I liv in. The new sheet is an update of properties sold. Each sheet uses unique "PIN" (Property Indentity Number) for every property record i the county. How do I match the "PIN's" from the new sheet to the old sheet t import and update three filelds ("Sale Year", "Sale Month", and "Sal Price") from the new sheet to the old sheet? Thanks, Rob Caldwel -- homevestor -----------------------------------------...

Counting the maximum value
Hi I have a column of amounts. I need to find the count of the maximum amount. For example if I have the below records $10 $9 $8 $9 $10 $9 then the maximum is $10 and the count is 2. How can I do it via a query? Thanks Regards Hi, try these queries #1 Derived table ---- SELECT tab1.amount, Count(tab1.amount) AS CountOfAmount FROM tab1, (Select Max(amount) AS MaxOfAmount From tab1) AS DrvTbl WHERE tab1.amount=DrvTbl.MaxOfAmount GROUP BY tab1.amount; ---- #2 SubQuery ---- SELECT tab1.amount, Count(tab1.amount) AS CountOfAmount FROM tab1 WHERE tab1.amount=(Select Max(amount) From...

create new record based on date
I'm not sure if this is even possible. I will have Frm_Main (from Tbl_MAIN), Subfrm_D13_Main (Tbl_D13_MAIN; one-to-many relationship with Tbl_MAIN) and Subfrm_D13_Inspection (Tbl_D13_Inspect; one-to-many w/ Tbl_D13_MAIN). In Tbl_D13_MAIN, there is a field Action with 3 options (Abandon, Reactivate, Suspend). Each action requires an inspection but if Suspend is selected then the inspection must be repeated on a certain frequency based on other fields (Risk_Level, Risk_Type and Downhole_Option). Here's some examples: If Risk_Level = LOW and Risk_Type = Type 1 then Inspection...

RMS PO's imported into Q-books should be as Item Receipt not Bill
Partial and completed purchase orders are pushed to Quickbooks as Vendor "Bills". They should be imported as "Item receipts" instead. Quickbooks gives the following procedures: 1. Receive Items and Enter a Bill 2. Receive Items 3. Enter a Bill for Received Items Using RMS should allow 2 and 3 to be done. At a minimum there should be an option to select how to handle PO receipts. Vendors send invoices/"bill's" to be reconciled against the "Item Receipt". Generally payment is made for an invoice not a packing slip/item receipt. The "...

Data entered in one record is shifting to others
I have a large spreadsheet that is attempting to track folk's location over the next few months. Columns A thru AE are data type entries....columns AF thru IU are date ranges. I am tracking/have entered over 300 records/rows. I have had some previous help on coding to shade the interior of the date cells based on what text entry that I make in the cell. Unfortunately, I didn't think of or ask for the code to return the cell interior color to white when i deleted the text entry...so I tried working the code so that it would. Well, it does, but only for one cell at a time. Any...

Count of Worksheets
How do you count how many Worksheets (tabs) in a Excel file. I know at one time I have a macro that went through each worksheet. I'm trying to get the number of worksheets and then loop thru all the worksheets and if the tab name of any start with "R-" copy the data from that worksheet to another to combine all the data from all the "R-" worksheets. If this makes since can anyone help me? Thanks Active.Worksheets.Count -- HTH RP (remove nothere from the email address if mailing direct) "Terry" <anonymous@discussions.microsoft.com> wrot...

RMS Inventory count with laptop and USB scanner
Can someone point me to info about doing a RMS Inventory count with laptop and USB scanner? On Jul 14, 5:47=A0am, <j> wrote: > Can someone point me to info about doing a RMS Inventory count with lapto= p > and USB scanner? I can tell you it is not very easy. We tried to do it in our grocery store a few times and gave up. Some of this issues we had were: 1. We would scan and count a bunch of items and then loose internet connection and SO would crash losing everything before we saved. 2. The quickscan did not always work well (although i cannot remember why ...

Count numbers formed from another formula
Hi. I'm trying to count a set of #s that is formed by another formula. For example, I have a list of numbers from c1-c6 which all have formulas in the cell which creates that number. When I try to do functions counting the #s, etc., it will not bring back a value. It doesn't want to count what has been formed from another formula. Please help! Thanks! -- Thanks! Stephen Normally, COUNT(C1:C6) will count all of the values in column A, including the zeros, but will ignore nulls ( "" ).............. Normally, COUNTA(C1:C6) will also include the nulls.......... What is ...

message's rules
Hello guys, I need to do rules for redirect of e-mails of for example: when i receive an e-mail eduardo@microsoft.com the server will redirect the message to a folder microsoft, but i need to do this in exchange 2003 SP2 and the server have to replicate for all users this rules. thanks a lot Not possible from within Exchange. Nue "Eduardo Mastro Pietro" <edufreire18@hotmail.com> wrote in message news:%233OPX44TGHA.736@TK2MSFTNGP12.phx.gbl... > Hello guys, > > I need to do rules for redirect of e-mails of for example: > > when i receive an e-mail eduard...

How can I find my Word Count for a Visio file?
might be a stupid question, but there you have it... Hello Indigotima, Word, being a word processor, has a range of clever methods for checking that sort of document information (Document.ComputeStatistics method for anyone who's interested), which Visio doesn't include in its object model. So not at all a stupid question but the short answer is no you can't. It's not there. You could achieve this with VBA or I think Paul Herber has a word count feature in his utilities add-on: http://www.visio-utilities.sandrila.co.uk/ Hope that helps. Best regards John John Gold...

Checkbox to change forms record source
Access2003 I have a form called fMOrderSearch. On the form I created an Option group called optRecSource using 2 checkboxes. Check56 -Option Value 1 and Check58 -Option Value 2 I would like to change the forms recordsource according to which check box is selected. If Option Value 1 SELECT ApprovalStatus, CCode, CCodeDetail, CurrentQueue, Desc, EDate, Yr1, Yr2, Yr3, Yr4, CustName FROM tNonconformance ORDER BY tNonconformance.EDate DESC;-- If Option Value 2 SELECT ApprovalStatus, CCode, CCodeDetail, CurrentQueue, Desc, EDate, Yr1, Yr2, Yr3, Yr4, CustName FROM tMYNon...

Get control's position in its parent
When I call SetWindowPos or use the DeferWindowPos functions to move controls on a form, the coordinates it takes are relative to the upper-left-hand corner of the parent window's client area. I can't figure out how to get the control's current location on the form, though. How do you get a child window's position inside its parent's client space? ~BenDilts( void ); "BeanDog" <BeanDog@discussions.microsoft.com> wrote in message news:89EACBAB-9232-4787-B7FC-5D177AFB20DA@microsoft.com... > When I call SetWindowPos or use the DeferWindowPos functio...

Select records with form driven input
I know this one is really dumb, but i can't seem to get the technique to work. Tables: MERIT BADGES containg a list of merit badges, SCOUT_ID containg info on personal, MB_RESOLVER conatins links between a Merit Badge and a Scout ID. So a Scout_ID can counsel many merit badges and a Merit badge can be Counseled by many Scout_ID's. Form: I would like a form that allows a Scout to select a Merit Badge (from a list box) and retrive all Counselors (by Scout ID) that work with that merit badge. Query: I have a query that retrives all records and required information. I ...