Hell everyone. I need some help with code. I have two tables with a 1 to many relationship. So, on my form I show the person and then in datasheet view in a subform I show all their assignments. I would like to be able to display counts of that persons Individual assignments on the main form. For instance, a judge could have 12 criminal cases, 15 domestic and 75 civil cases all listed in the subform. But, I want to show the breakout totals on the main form.

Any Ideas, code would be highly appreciated.

Thanks,

Ru1

There are many ways to do this Ru1. Here are two ideas, a simple quick and dirty one and a better one (my favorite). You will need to adapt the examples to your fields and tables name of course.

1 - Using the DCount agregate function.
This function works very well and you can type it right into a text box on the form. The only drawback is that it is slightly slow and needs to be recalculated every time you change record in the main form. So...

=DCount("*","tblCases","JudgeID=" & Me!JudgeID & " AND " & "CaseType=1")

The text box containing this expression will display the number (count) of cases where the JudgeID is the same as the one displayed on the main form AS WELL AS the type of assignment equal to 1. Hopefully all the type of assignments are in another table and you created a combo box looking up the type.

Now simply copy and paste the expression in the other text boxes and change the value for CaseTypeID (2, 3, ...) with a label in front of each describing the type of assignment.

2 - Using a summary query (my favorite but more work)
You can also create a query that uses Grouping on JudgeID and CaseTypeDescription with a Count on any non-null field from the tblCases table. The query result would look like something like this:

JudgeID CaseTypeDescription CountOfCaseD ate
1 Description 1 4
1 Description 2 3
1 Description 3 8
2 Description 1 5
2 Description 2 7
... and so on.

Now the finishing touch. Create a form based on this query and insert it in the main form as a subform. The LinkMaster and LinkChild fields will be JudgeID.

If you want you can even add an expression such as:
=SUM(CountOfCaseDate) in the Form Footer of the subform to have a grand total of all the assignments. In your main form you would see something like this:

Description Count
Description 1 4
Description 2 3
Description 3 8
--------------------
Total 15

This approach is better in the sense that the figures will display very quickly and will seem to be part of the main form.

Hope you will let us know how it worked.

Daniel

Thanks Dan! It worked like a charm. I had to make some modifications becasue of the way my data came in, but it is working very well. Thank You!

RU1