Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 25th, 2005, 12:37 PM
ru1 ru1 is offline
Authorized User
Join Date: Feb 2005
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default Under the Gun Need Help!

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.



Old February 25th, 2005, 01:30 PM
Authorized User
Join Date: Feb 2005
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Old February 25th, 2005, 03:49 PM
ru1 ru1 is offline
Authorized User
Join Date: Feb 2005
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts

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!


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.