Group linked to one record and copy to another

[mcboe]

Hi there,



First post so please excuse me if I haven't added this to the right place or if this has already been discussed. As you all know, individual scenarios present their own unique challenges.

For me, I have entities that have associated "People Groups" (Ex.: Officers). In each of those groups, I have the individual people that are linked to that group.

What I'm hoping to achieve is a way to copy one group of people (Ex.: Officers) from one Entity (Ex.: ABC) to another entity (Ex.: XYZ). To do so, I've created a pop-up for my users to select the entity and then the group (Ex.: ABC and then Officers). They would access this through the new entity's form view (Ex.: XYZ). Once they click 'Save' (or otherwise), the group of Officers from ABC would be copied to XYZ as Officers (highlighted in yellow).

Now, in the preliminary research I've done, it seems like 'append query' is the route to go. However, I do not have the know-how to figure out how to create a new "People Group" with the same information as the old "People Group" while also changing the Entity associated with the group.

Below you'll find the relationships for the tables used and the examples again in the blue boxes.

I appreciate any help that anyone can provide!

All the best,

MB

[June7]

Why is Entity field in two tables?

Why is the contacts foreign key field in tbl_GroupContacts called FirstName? Better would be ContactIDfk.

I would run an SQL statement in VBA, like:

CurrentDb.Execute "INSERT INTO tbl_GroupContacts(ContactIDfk, Committee) SELECT ContactIDfk, {XYZ committee ID here} AS Committee FROM tbl_GroupContacts INNER JOIN tbl_Group_People ON tbl_Group_People.ID = tbl_GroupContacts.Committee WHERE Committee = {ABC committee ID here} AND tbl_Group_People.Entity='Officers'"

The {} characters would be removed in actual statement. If using variables to hold inputs for committee and entity, concatenate to build the SQL statement.