• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Track counters on "children" in "parent" record ?

Hello,

I have a simple database with to 2 tables:

   Event
   Participant

I created a one-to-many relationship between these 2 tables.

Now I would like to create a field in the parent table called:

   Count_Participants

In this field I would like to store "automatically" how many Participants (children) where created for the specific Event (parent).

So if a participant is added for an event, this count would be count+1.
If a participant is deleted, there should be 1 subtracted from the count.
And this without any manual user interaction offcourse... ;-)

Any help is more then kindly appreciated !!!

Jean

0
JeanFranck
Asked:
JeanFranck
  • 4
  • 3
  • 3
  • +2
1 Solution
 
JimMorganCommented:
There are a couple of ways to do this.

You could run an update query after all of the particpants are entered into that table, which counts the records for an event and updates the field in the events table.

As you are entering the particpants in a form, after you add the record, update the counter   Count_Participants
= DLast("Count_Participants", "Events", "Event = " & Event) + 1.  The same routine could be used when a participant is deleted.  However, you would have to handle the deletion manually with a command button.  I'd bring the field into the data entry form even if it is hidden.  It makes it easier to have Access do the work of updating the table.

Unless you need to see the current count on the participant entry form, I would recommend running an update query after the records were entered and/or  deleted.

Jim
0
 
DedushkaCommented:
I don't recommend to do this at all, i.e. to store the count of "children" records in the table or somewhere else.
In theory you should calculate it every time, when you need it.
Jim, what do you think about that?
0
 
cjswimmerCommented:
I agree with Dedushka.  A value that is a resulted calculation of two or more other stored values should never be stored itself.  It should always be calculated when needed.  This is one of the fundamental principles of database design and should only be violated when performance is compromised to the extreme by determining this amount 'on the fly'.  By ignoring this rule you run the risk of violating data integrity in your tables.
CJSWIMMER
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JeanFranckAuthor Commented:
Nice to read your theories.

Since I'm working in an ADP project I don't have to possibility to use update queries.
The others recommend to calculate when needed.
The only place where my client wants to see the count of participants for a particular event is on the event-form itself... nowhere else.

It's a single form, not continuous.

Hope you can recommend me again and explain how I can calculate and when...

Many, many thanks !

Jean
0
 
JeanFranckAuthor Commented:
Nice to read your theories.

Since I'm working in an ADP project I don't have to possibility to use update queries.
The others recommend to calculate when needed.
The only place where my client wants to see the count of participants for a particular event is on the event-form itself... nowhere else.

It's a single form, not continuous.

Hope you can recommend me again and explain how I can calculate and when...

Many, many thanks !

Jean
0
 
cjswimmerCommented:
If you don't mind the results not being updatable, make the recordsource of the form you need to display the results of a query that uses aggregate functions.  Buils a new query joining the two tables and then press the "Totals" button on the toolbar (Looks like an E).  Under a field that is a unique identifier for the participants records and change the Total Row action to "Count" and give this column a new fieldname (or stick with what Access names it as 'Count of <fieldname>').
Set the recordsource on your form to the name of this new query, then set the textbox's controlsource on this form to the fieldname that contains this count.  Please let me know if I am not being clear.   Good luck to ya.

CJSWIMMER
0
 
BrianWrenCommented:
I think that you should exercise full control over this field through the user objects that can change its value.

Once you get the right number inot the field, use the AfterInsert of whichever form is doing the adding to increment the field's value.

Perform similar actions on the basis of action taken to delete entries.

(As Jim recommended.)

Brian
0
 
cjswimmerCommented:
What is the reason that you both (BrianWren and JimMorgan) believe in storing this computed value instead of figuring it in a query?
0
 
BrianWrenCommented:
JeanFrank said, "Since I'm working in an ADP project I don't have to possibility to use update queries."

Brian
0
 
DedushkaCommented:
In this particular case I'd prefere to use domain aggregate functions, something like this:

locate a textbox for counter and set its value to
=DCount("ParticipantID", "Participant", "EventID = " & me.EventID & ")"

When you should recalculate this counter or refresh your form depends from the form design.
0
 
JeanFranckAuthor Commented:
I would have given Excellent if the syntax of DCount was correct but I had to experiment a bit with ' and & and " to make it work.  ;-)
Nevertheless many, many thanks for sharing your knowledge !!!

0
 
cjswimmerCommented:
Well done Mr. "Expert of the Week"  :-)
0
 
DedushkaCommented:
Thanks all.
Have a nice weekend.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now