Counting Parent Records and then Child Records on a Continuous Form after a filter is applied. (1 Viewer)

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
I have a continuous form that is based on a child table so that all records are present. When I use the Count(*) function in an unbound text box on the form it gives me the total count of all records that are displayed on the form. However, I also need to know how many Parent records exist. Since the form is based on the Child records there may be multiple occurrences of the parent record key showing. Is it possible to count unique occurrences of the parent record by using the Count function or some other function to count unique values of say, the Parent Record ID field or something like that so that I can show "287 Total Patient Records" and then in the other unbound text box, "386 Proceedures Performed"

Much thanks in advance!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:13
Joined
May 7, 2009
Messages
19,169
You can create a Function in your child form that will use recordset:

Public function fnParenCount() as long
Dim db as dao.database
Dum rs as dao.recordset
Set db=currentdb
Set rs=db.openrecordset( _
"select parentId from (" & me.recordsource & ") group by parentId;")
With rs
If not (.bof and .eof) then
.movelast
.movefirst
fnParentCount=.recordcount
End if
.close
End with
Set rs=nothing
Set db=nothing
End function
 
Last edited:

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
You can create a Function in your child form that will use recordset:

Public function fnParenCount() as long
Dim db as dao.database
Dum rs as dao.recordset
Set db=currentdb
Set rs=db.openrecordset( _
"select parentId from (" & me.recordsource & ") group by parentId;")
With rs
If not (.bof and .eof) then
.movelast
.movefirst
fnParentCount=.recordcount
End if
.close
End with
Set rs=nothing
Set db=nothing
End function
Thanks for the info, so would this function be coded on the actual child forms vba coding area?
Would I then somehow assign the function to an unbound textboxl on the form to get the result to show?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:13
Joined
May 7, 2009
Messages
19,169
if you want to show the "Total" on the Main form, put it on the main form and call it (see Text1 Controlsource of Form1 in design view).
if you want to show in subform (like in continuous form) put the code in the subform (see the code).
 

Attachments

  • ParentCount.accdb
    640 KB · Views: 150

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
if you want to show the "Total" on the Main form, put it on the main form and call it (see Text1 Controlsource of Form1 in design view).
if you want to show in subform (like in continuous form) put the code in the subform (see the code).
Wow that was quick and works perfect on your form. However, I guess I have built mine differently, because when I change your form to a Continuous form I get an error message
DBErrorCF.PNG

So my form (fAccListMSLB) is based on a query that has two tables joined together.
* The parent table is tAccLog and the unique key is tSpcID.
* The Child Table is tAccSpc with a unique key tSpcID and the linking field is tAccNoLink that contains the same value as tAccId in the tAccLog.

For every Record in tAccLog (Parent) there is one or more records in the tAccSpc (Child Table).
The Form is a continuous form that allows the user to see every record record in the tAccSpc and the Name, Provider and Staff Processor of that specific event from the Parent Table and also the details of the actual event from the Child Table.
So I have 8,956 in tAccLog (Parent) and 18,539 records in tAccSpc (Child)

The User is presented with 5 Multi Select List boxes that allow them to filter the records to get exactly what they want by click the values that correspond with their need, for Example, they may just want to see the Year 2021 and say the 2nd quarter, so they click 2021 in the year box and April, May and June in the Month box, and then they only want records for the Provider Name John, they click him and the staffer that recorded the event, maybe Jane. So now there are only 387 total records for the tAccSpc (Child) table, which I get by simply using the =Count(*) but there are only 42 unique Parent records. for the 387 child records... So... counting the Parent records is my struggle. I had thought initially that I could just create an unbound text box and do something like "Count(tAccNo)" but that still counts 387 records because for each child record it shows the tAccNo for them. So then I thought I could somehow count only the first occurrence of each tAccNo... but so far, nothing is working.

So perhaps this is a better explanation and can give you a better idea of what I was trying to do. But let me say a BIG THANK YOU for that example that you sent! It was above and beyond the call of duty on your part!!! ☺
 

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
Wow that was quick and works perfect on your form. However, I guess I have built mine differently, because when I change your form to a Continuous form I get an error message
View attachment 100930
So my form (fAccListMSLB) is based on a query that has two tables joined together.
* The parent table is tAccLog and the unique key is tSpcID.
* The Child Table is tAccSpc with a unique key tSpcID and the linking field is tAccNoLink that contains the same value as tAccId in the tAccLog.

For every Record in tAccLog (Parent) there is one or more records in the tAccSpc (Child Table).
The Form is a continuous form that allows the user to see every record record in the tAccSpc and the Name, Provider and Staff Processor of that specific event from the Parent Table and also the details of the actual event from the Child Table.
So I have 8,956 in tAccLog (Parent) and 18,539 records in tAccSpc (Child)

The User is presented with 5 Multi Select List boxes that allow them to filter the records to get exactly what they want by click the values that correspond with their need, for Example, they may just want to see the Year 2021 and say the 2nd quarter, so they click 2021 in the year box and April, May and June in the Month box, and then they only want records for the Provider Name John, they click him and the staffer that recorded the event, maybe Jane. So now there are only 387 total records for the tAccSpc (Child) table, which I get by simply using the =Count(*) but there are only 42 unique Parent records. for the 387 child records... So... counting the Parent records is my struggle. I had thought initially that I could just create an unbound text box and do something like "Count(tAccNo)" but that still counts 387 records because for each child record it shows the tAccNo for them. So then I thought I could somehow count only the first occurrence of each tAccNo... but so far, nothing is working.

So perhaps this is a better explanation and can give you a better idea of what I was trying to do. But let me say a BIG THANK YOU for that example that you sent! It was above and beyond the call of duty on your part!!! ☺
I would note that the code you gave me does provide a total count of the tAccNo records that have connected tAccSpc records. But that number does not refresh or update when I select items in the Multi-Select List Boxes. I wonder if it would if I added some kind of refresh or requery to the after update box, or a command to refresh after each click on the MSLB's
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2002
Messages
42,970
SQL server has the syntax to count distinct but Access does not. In order to use a query to count distinct, you need two queries. One query to group the items you want to count so that the distinct value occurs only once. The second query counts the rows returned by the first query. You can also do this using one query with a subselect. I tend to avoid subselects when there are alternatives because Access does not optimize subselects well. Separating into two queries gives Access the jolt it needs to do it right. Access combines the separate queries in its execution plan.

So, you can use VBA to loop through the open recordset or you can run a dcount() on the grouped query.

You would need to run the dCount() after you filter the recordset.
 

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
SQL server has the syntax to count distinct but Access does not. In order to use a query to count distinct, you need two queries. One query to group the items you want to count so that the distinct value occurs only once. The second query counts the rows returned by the first query. You can also do this using one query with a subselect. I tend to avoid subselects when there are alternatives because Access does not optimize subselects well. Separating into two queries gives Access the jolt it needs to do it right. Access combines the separate queries in its execution plan.

So, you can use VBA to loop through the open recordset or you can run a dcount() on the grouped query.

You would need to run the dCount() after you filter the recordset.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2002
Messages
42,970
@kengooch you copied my post but didn't say anything. Are you switching to the query? Did you have a question?
 

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
SQL server has the syntax to count distinct but Access does not. In order to use a query to count distinct, you need two queries. One query to group the items you want to count so that the distinct value occurs only once. The second query counts the rows returned by the first query. You can also do this using one query with a subselect. I tend to avoid subselects when there are alternatives because Access does not optimize subselects well. Separating into two queries gives Access the jolt it needs to do it right. Access combines the separate queries in its execution plan.

So, you can use VBA to loop through the open recordset or you can run a dcount() on the grouped query.

You would need to run the dCount() after you filter the recordset.
I'm not sure how to go about this, and given your concern of subselects, I guess I would want to use a VBA Loop to count the recordset after the filter is supplied, however, Im not sure how to create that loop? Would I just call it after the last click on the Multi-select list box, or would I put it in the AfterUpdate area?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2002
Messages
42,970
The method I suggested does NOT use a subselect. Please read it again. I added the reference to a subselect because people who normally work with SQL Server would recommend it.

Also, the efficiency of a query or the length of time taken to process a DAO read loop of a recordset are directly dependent on the number of rows in the recordset. If the recordset is small, then you won't experience slowness. If the recordset is large, you will and so you need to look for efficiencies. In all cases, queries are faster than DAO/ADO read loops.
 

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
The method I suggested does NOT use a subselect. Please read it again. I added the reference to a subselect because people who normally work with SQL Server would recommend it.

Also, the efficiency of a query or the length of time taken to process a DAO read loop of a recordset are directly dependent on the number of rows in the recordset. If the recordset is small, then you won't experience slowness. If the recordset is large, you will and so you need to look for efficiencies. In all cases, queries are faster than DAO/ADO read loops.
Given the fact that I am using Multi Select List Boxes to Filter my records, I don't know how to pass that filtering to a query that could track the counts as the user clicks more and more option. I think that I gave a better description of what I have created and what I am trying to achieve on my reply to Arnelgp above. Also, I do have a lot of records as noted in that post, so efficiency and speed are important to me.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2002
Messages
42,970
If you are happy with what you have, I don't need to build an alternative for you. Here's a link to a database with a sample that shows how to build an In() clause.

The In() makes my suggestion more complicated than it would have been otherwise but still easy enough if you need it. You can't pass an In() clause to a query so you would need to build the SQL in VBA and save it as a queyrdef or update an existing querydef. Then you can reference the saved querydef in the dCount()
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:13
Joined
May 7, 2009
Messages
19,169
if you can share the db and tell me which form/subform to work, i am sure i can modify the code to include Count with "filtering".
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:13
Joined
Sep 21, 2011
Messages
14,044
Couldn't you just MoveLast on recordsetclone and get record count?
 

kengooch

Member
Local time
Today, 08:13
Joined
Feb 29, 2012
Messages
137
if you can share the db and tell me which form/subform to work, i am sure i can modify the code to include Count with "filtering".
How would I go about sharing it with you? It is a very large database and it is split into a front end and back end. I would have to send a copy and delete all the database because it is a medical database and subject to HIPPA rules. Any suggestions on what to do?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2002
Messages
42,970
Couldn't you just MoveLast on recordsetclone and get record count?
There are two counts involved. A count of the details which is what you get with the MoveLast and a count of the parents which takes a separate query because first you have to group them since they repeat in the recordset.

You can count them by reading the recordset clone as long as it is sorted by the parentID but you need break logic to do the counting.
 

Users who are viewing this thread

Top Bottom