Mail Selected Officer

[AndyWil]

I hope you can solve this......



I have 80+ Groups each with their own set of Officers e.g. Chair, Secretary, Treasurer, Safeguarding Officer etc.

I have a Contact_Tabel with Names, Emails, Address etc.
I have the Group_Tabel with links to each type of Officer
Chair - Number
Secretary - Number
etc.

So I Have a form where you select the officer you want to mail - e.g. all Secretaries
Is it possible to do this with on Query
I could create seven Identical queries, one for each office - is there another way?

I was considering having an extra field MM_Officer & use an Update Query to copy the selected Office into it for the Mail Merge?
I'm still not sure how to do that.

The only other way would be to write code to do this record by record - but I would love to learn more about Query's

Thanks

Andy

[Gicu]

Can you post a small sample of your db (no real data, just some "dummy" records)? It should be very easy to do it with one query that takes the office type from a combo on your form. So the combo would list the OfficerTypeID, OfficerTypeName (set its row source to "SELECT OfficerTypeID, OfficerTypeName FROM tblOfficers;"). Set the combo to have 2 columns and set their widths to 0" and 2" (so you hide the ID and display the name). In another query you show the info from the contact table where officertypeid =Forms!frmYourForm!cboOfficer (this method is called query by form). You will need of course to use your own table/field names in these examples.

Cheers,

[AndyWil]

Hi Gicu,
Thank you so much for your reply, I ended up writing code as I needed to add a further condition.
If an officer post (say Chair) is not filled then default to the Secretary.
It's highlighted to me that I need to learn SQL - I've just muddled through using the Query Builder - (being an amature)

One problem I do have with my workaround is that if the Secretary post is not filled then the Group does not show in the listbox
So I have a ListBox showing all the Groups with the Selected Officer e.g. Chair, Secretary etc.
When you select the type of office you want to mail to, for instance Chair - the code copies all the Chair links to a MailMergeTmp link in the Record Set. We only have 80 groups.

Groups
Group Name String
Secretary -> ContactRecords
Chair -> ContactRecords
MailMergeTmp -> ContactRecords

So the Listbox uses the Mailmergetmp field to display the Selected Officer
My problem is, if there is No Secretary the Group does not show in the ListBox

Is it possible to still list the group and highlight that there is no one to mail to in this group?

Thanks

Andy

[NoellaG]

If you have a Group_Tabel with every group in it, you can create a groupsquery from this table with an outer link to the groups-field in MailMergeTmp. Group by groups type and count the number of contacts per group and use this query as rowsource for the listbox.