Fields in table created with Relationship & Lookup data type, have querying issue.

[McArthurGDM]

So,

I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values, as you can see below:




It creates a LIST of values for that cell.

When I query a particular value in this table I get every possible combination of the value. My query and code are below:



OR,



SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update, MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value, MainT.[Ref#ID], MainT.[Issuing Agencies].Value
FROM MainT
WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null) AND ((MainT.[Entities Impacted].Value) Like "*" & [Forms]![SearchF]![USEntitiesCB] & "*" Or (MainT.[Entities Impacted].Value) Is Null) AND ((MainT.Update) Like "*" & [Forms]![SearchF]![Updatetxt] & "*" Or (MainT.Update) Is Null) AND ((MainT.[Divisions Impacted].Value) Like "*" & [Forms]![SearchF]![SubfieldCB] & "*" Or (MainT.[Divisions Impacted].Value) Is Null) AND ((MainT.Stakeholders.Value) Like "*" & [forms]![SearchF]![StakeholderCB] & "*" Or (MainT.Stakeholders.Value) Is Null) AND ((MainT.[Ref#ID]) Like "*" & [forms]![SearchF]![Ref#ID] & "*" Or (MainT.[Ref#ID]) Is Null) AND ((MainT.[Issuing Agencies].Value) Like "*" & [forms]![SearchF]![AgencyCB] & "*" Or (MainT.[Issuing Agencies].Value) Is Null));


This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.

The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying. As you can see in the picture below:




notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?!

Perhaps there is some SQL code that can fix this issue? If so, let me supply you with the field names and other information you need to help me write the particular code.

I could really use some help!

[June7]

Related thread https://www.accessforums.net/queries...ion-45431.html

I think the issue is use of multi-value fields and expanding all the multi-value fields in the same query.

In a conventional relational database, each of the multi-value fields would be a related child table.

I have NEVER used multi-value fields.