How connect a attendant table to a liste ofparticipants and a series of meetings


this is a add on a previuos question already answered by als335.
The solution proposed was to creat a form where the user will populate a list of attendant from a list of potential attending people. This works fine, but I have to do that thing automatically
The subform must look who could be there and modify acccordingly the list of attendant people

 So I have a liste of people.. that doesn't change often (will be changed manually when necessary)
 I have also a list of meetings summaries with a lot of variables and a subform
 RCP_Meeting  (Réf_Meetinf, Date_Meeting, var 1, var 2, .., var 100)
TaskForce (Réf_Attendies, Name_Attendies)

What I need is a way to add to the main form a subfrom which will add the potential attendies and a way to ssay who is there.. And of course input the results in a table.. i.e.
creat a Attendingtable with (Réf_Meeting, dateMeeting, AttendantName)

I was able to work on als335 but could not create the last table. If I add a name on the taskForce list, it will not show up in the attendingtable.

I could really appreciate a last push to solve the issue

Best regards

Jacques WeissenburgerAss ProfAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
Not sure how you have already created a Form(s), ....without your relationships being firmly established...?
Not really sure of your existing relationships or how a meeting can have many "Summaries"...?

...So you should really post a link to the previous Question for us to refer to...

I would avoid terms like Attendee and Participants, ...its too confusing, as most people could use either one of those terms to describe a person IN a meeting...

In the most basic sense, you could have something like this:

pID (PK)

mID (PK)

tblPeopleMeetings (Many-To-Many table)
pmID (PK)
pm_pID (FK)
pm_mID (FK)

sID (PK)
s_mID (FK)

So in this case, One person can be in many meetings,  and also, one meeting can have many People (fairly common, many to many setup)
Also one Meeting can have many summaries...

Now, depending on how you would like your data entry to be done, would create your forms (Again, ...*After* the relationships are established)

Jacques WeissenburgerAss ProfAuthor Commented:

I apologize for my bad english..  

Here is the link

and also a modified powerpoint to clarify my question

Thanks for your help
Jeffrey CoachmanMIS LiasonCommented:
oK, so what I proposed was basically the same as what als335 has in his sample, what is your question?
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!

Jacques WeissenburgerAss ProfAuthor Commented:
Als335 prpose a nice solution. What I need is to simplify the process for the animator of the meeting
A way to avoid the rigth subform.. on his exemple

During our medical meetings (once a week), a lot of clinical situations are studied and the 'paper work' must be as light as possible.
For every 'case', a report is made, and the end of the report must indicated who was there to make the therapeitic choice, and this report is put in the patient folder.

I join here the DB itself and a powerpoint with more explanations (to go fast, look on slide 3 for the relation table and 6 for the report I need to ameliorate). Hope it will help

Thanks anyway for every help

Jeffrey CoachmanMIS LiasonCommented:
<A way to avoid the rigth subform.. on his exemple>
It is not clear what the right subform's purpose is..?

We need to keep the focus of this question fairly narrow.

Again, you have people who attend meetings, ...correct?
You want a form to assign people to meetings correct?
What is "wrong" with Als335's approach?
If it works, ...there may not be a simple way to "avoid" the right subform.
Jacques WeissenburgerAss ProfAuthor Commented:
My people will not accept to populate the left subform from the right one for every report. This must be done automatically...

Let us simplify things
  I have a table of praticians..  (IDPratician, Praticianname, PraticianService)
  I have also a table (tbl_meeting) with data from meetings including RéfMeeting and DateMeeting

I want to populate a 3rd table (tbl M_A) with
    for each DateMeeting (table : tbl_meeting)
       and each Praticians (from table : tbl_Pratician)
           a new line of data :  IDPratician, PraticianName, PraticianService, RéfMeeting, DateMeeting and Missed (a checkbox)

This must probably be done by VBA code,  as a subroutine called each time a new meeting date is created

Once this is done,  I will be able to use the as335 solution.

I am looking for help for this code.. It is not easy for me..

Thanks for your  help

Unless the code is prescient, how would it "know" which people attended a particular meeting?  I don't see how code will solve this problem.

If most people attend most meetings then maybe the solution is to populate the attended table by using an append query that selects from the list of people and appends all of them to the meeting attended table.  Then the clerk would need to scroll through the meeting attended table and delete the people who didn't actually attend.  This negative process would work best if you used a pre-printed sign in sheet.  List everyone's name and add a checkbox.  They check the box when they sign in.  If their name isn't already printed, they write it in at the bottom.  Using this list it would be fairly easy to identify who didn't attend and delete them.  Then manually add the unexpected attendees.

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
Jacques WeissenburgerAss ProfAuthor Commented:
Hi PatHartman,

Happy to find you here, you already helped me on this DB (or another).
You are right, sometimes a pencil and a piece of paper is the best solution. But not here.

I try to do the job automatically, almost at least. I use the method proposed by as335.. to input who is there from a fixed list. This works fine..  BUT, but... this doesn't populate the table of attendees. For some ISO9001 rules, my people have to 'trace' the type of professionnal who sign the decisions and be able to do an annual report.  
Therefore I need what I said..Include during every meeting, the list of the present people into the "table of present people".. The as335 solution allows to check and trace who is there during the session, but not to keep trace of it and cumulate data over the time.
So I need to pre-build a table every time I create a new meeting.. and use that 'prebuilt' table to check latter who is there or not..  Something like ....
For every new record of a meeting  : Tbl_meeting (IDMeeting, DateMeeting, ,.,.,. other data)
          add as many lines (say n lines) in the table of present people (tbl_Prat&Meet)  as I have praticians in the "table of pratician (Tbl_Prat..)"    
      In Tbl_Meetings I will have (IDMeeting, Date Metting and a lot of other data)
      In Tbl_Praticians I will have (IDPratician, NamePratician, Speciality) and nothin else
This code should include into Tbl_Prat&Meet   n lines, each line with
                    Ref_Meeting, DateMeeting, (n times the same)  and in each of these n lines : NamePratician, speciality, and a checkbox called Present, checkbox who will be treated separatly with as 335 tool

These tables are related with one to many junctions from Tbl_Meeting to Tbl_Prat&Meet and from Tbl_Praticians to Tbl_Prat&Meet

 I am really not so familiar with recordset and am afraid of doing unstable things

Thanks for any help (as you already did some times ago)
Sorry, due to the language difference it is simply too time consuming for me to try to figure out how this application works.  

Looking at the PPT, I would expect that when you check the box next to the doctor's name on pg 7 of the PPT that would constitute attendance at the meeting.  Either this form needs to be bound to the junction table or checking the box should insert a row into the junction table.  If you use the second method, don't forget that if you uncheck a box, you would need to delete a record from the junction table.
Jacques WeissenburgerAss ProfAuthor Commented:
I found the solution working on the Apped Quey idea of PatHartman and it works pretty fine..
  I create a query with 5 fields
     IDMeeting and DateMeeting by criteria obtained through the form were a button launches the signature action
     and all the other coming from the pratician table (3 fields : IDpratician, NamePratician, Department)

  Than I transformed that query into an append query, to add the records to a cumulative table with 6 fields. 5 taken from the preceding query and a 6th which is a checkbox created on the cumulative table. That check box with stay empty until he will be checked using as335 solution

  Thanks for your time listening to me and helping to find the solution

Looking forward
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.