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

JeanFranckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.