Simple Microsoft Access 2016 Query with a parameter request

You seem to have used several multi-value fields.  They cannot be used in relationships or joins.  I would suggest starting again and creating a proper schema with two tables and give up on the multi-value abomination.

On the surface, multi-value fields look useful and you get a cool control to use to manage them.  In reality, they have little use once you get beyond the simplest application that doesn't use code or queries.  Once you have learned enough to graduate to code and queries, they requie specialized processing.  I don't use them so if you want to stick with them, you'll need to wait for someone who does to step in.

I understand why you might have multiple affiliations but I don't understand why you would have multiple counties.  If County goes with affiliation, then you will have no option but to remove BOTH fields and start again with a proper 1-m relationship.

Thanks Pat.  Some cities are split by county lines resulting in more than one county.  But really, that's no big thing to change to one county.

How else can I show that someone has more than one affiliation besides adding him to the table multiple times?

Offical
officialID (primary key)
namefirst
namelast
*

County
countyID (primary key)
name
*

Affiliation
affliationID (primary key)
officialID (foriegn key)
countyID (foreign key)


Sample Data:

Offical
1,john,doe
2,jane,doe

County
1,alba
2,alto

Affiliation
1, 1,1
2, 2,1
3, 2,2

(in this example: John Doe is affiliated with only Alba, while Jane Doe is affiliated with BOTH Alba and Alto). This allows MANY officials to be affiliated with MANY counties.

Table [Offical] forms a one-to-many relationship with table [Affiliation]. table County does the same thing with [Affiliation]. Ultimately the table relationship forms between [Offical] and [County], called a many-to-many relationship (3 tables).

Now the entry becomes numerical for [Affiliation] for the corresponding OfficialID and CountyID. Beyond the ability to JOIN fields and run queries on the data, this best practice affords less error (imagine all of the typos that could happen in a text entry field) and eliminates data duplication as well.

The hypothetical UI/UX would be a form to add to the Affiliation then becomes simply two dropdown inputs and a submit button (or a list of currently associated counties if the form depends on the Offical being selected already); This is more advanced but I wanted to mention it for fun:

OfficalID (user selects the official from the list of names; in the background, the form returns the OfficalID for the Affiliation table insert)
CountyID (users select the county from the list of names; in the background, the form returns the CountyID for the Affiliation table insert)
INSERT new record (null, OfficalID, CountyID) *null is for autoincrement*

Multi-valued field is a technology, introduced with Office Access 2007, to simplify tasks, where the Access database engine does the necessary many-to-many relations.
You don't need to change your design, but you need the knowledge to retrieve the data.

Check this Microsoft article about Using multivalued fields in queries.

If the proprietary technology is going to be used, I concur that learning both the fundamental and novel methodologies are vital to understanding how it works.