Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 25th, 2005, 12:37 PM
ru1 ru1 is offline
Authorized User
Join Date: Feb 2005
Location: Detroit, MI, USA.
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.



Reply With Quote
  #2 (permalink)  
Old February 25th, 2005, 01:30 PM
Authorized User
Join Date: Feb 2005
Location: Frankston South, Victoria, Australia.
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.

Reply With Quote
  #3 (permalink)  
Old February 25th, 2005, 03:49 PM
ru1 ru1 is offline
Authorized User
Join Date: Feb 2005
Location: Detroit, MI, USA.
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!


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

All times are GMT -4. The time now is 10:04 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.