Simple Microsoft Access 2016 Query with a parameter request

I have a very simple "directory" database with two tables. One is the Contacts and the other is the affiliations table.  I created a query with a parameter request on the affiliations field so that I can get a report of contacts from a certain city or county.  When I run the query, I get the error "Expression is typed incorrectly, or it is too complex to be evaluated."  Is it because there are some contacts with many affiliations?

Before I had two tables, I had one table with some contacts in multiple times because of their multiple affiliations.  The query worked fine then.
ScreenHunter_879-Dec.-21-12.55.jpg
ScreenHunter_880-Dec.-21-13.01.jpg
ScreenHunter_881-Dec.-21-13.01.jpg
ScreenHunter_882-Dec.-21-13.01.jpg
ScreenHunter_883-Dec.-21-13.01.jpg
ScreenHunter_884-Dec.-21-13.02.jpg
MLGIS Program ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

PatHartmanCommented:
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.
MLGIS Program ManagerAuthor Commented:
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?
Fabrice LambertConsultingCommented:
How else can I show that someone has more than one affiliation besides adding him to the table multiple times?
Basically, with a many to many relationship.
This is done with an additional table that will hold keys from tables A and B.
So in reallity, that's 2 one to many relationships involving 3 tables.
A (n, n) B = A (1, n) C + B (1, n) C

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
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

NerdsOfTechTechnology ScientistCommented:
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*
Hamed NasrRetired IT ProfessionalCommented:
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.
NerdsOfTechTechnology ScientistCommented:
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.
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
Databases

From novice to tech pro — start learning today.