Query Multiple Fields for the same value to return a list of records (primary keys)

I have a database (Access 2013) where I store modifications done to engineering parts. I have 10 fields each with the same lookup so that users can select multiple parts the modification has been applied to.

ModID: MODIFICATION-001 (Primary Key)
PartAppliedTo1: Example1
PartAppliedTo2: Example 2
PartAppliedTo3: Example3
PartAppliedTo4: Example 4
PartAppliedTo5: Example 5

I need to be able to query all of these fields (PartAppliedTo1-10) with a part name and return a list of modifications that have been done to this part.

It works if I only apply the criteria to one of these 'PartAppliedTo' fields but multiple fields just returns no value.
Thomas ReidMaintenance TechnicianAsked:
Who is Participating?

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

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.

SujithData ArchitectCommented:
Are you looking for something like this?

select <columns>
from <your table>
where (PartAppliedTo1 = <your part name> OR PartAppliedTo2 = <your part name> ..... OR PartAppliedTo10 = <your part name>)
Thomas ReidMaintenance TechnicianAuthor Commented:
Hi Sujith,

I am a complete novice at databases.
Would this code be input to the query in SQL view?

PartAppliedTo1 would need to be equal to a form control
e.g. WHERE PartAppliedTo1 = [Forms]![NavigationForm]![NavigationTitle]
And if it does not find it in PartAppliedTo1 then it looks for the same value in PartAppliedTo2, 3, 4...etc
SujithData ArchitectCommented:
Can you extend you query like (assuming your data model)

WHERE ( PartAppliedTo1 = [Forms]![NavigationForm]![NavigationTitle]  OR PartAppliedTo2 = [Forms]![NavigationForm]![NavigationTitle] ..)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Thomas ReidMaintenance TechnicianAuthor Commented:
This seems to have worked. Is there any way however that if a value appears in PartAppliedTo1 then it cannot be input into any of the other PartAppliedTo#?

For example if a part was input in PartAppliedTo4 then it could not be input in any of the other PartAppliedTo fields?
SujithData ArchitectCommented:
Isn't that a data entry restriction you are asking about? I don't know how data gets into your table. You should probably build a validation in the data entry form that the same part name is not keyed into multiple columns.
Dale FyeOwner, Developing Solutions LLCCommented:
Both of these items would be far easier if those columns PartAppliedTo# were rows in another table rather than columns.

It would simply be something like:

SELECT MODID, SeqNum, PartAppliedTo
FROM yourTable WHERE [PartAppliedTo] = "01234"

With this type of structure you could add as many "PartAppliedTo" values as you want, and would not be limited to the 10 fields you currently have.  Also, when you create a new record for each ModID, you would simply increase the SeqNum.  And before saving a record, you could easily check to see whether that PartName already existed for that ModID with something like:

strCriteria = "([ModID] = " & forms!yourForm.txtModID & ") AND (PartAppliedTo = '" & forms!yourForm.txtPartAppliedTo & ') " _
                   & " AND ([SeqNum] <> " & me.txtSeqNum & ")"
if DCount("*", "yourTable", strCriteria) > 0 then 
    msgbox "PartAppliedTo already exists for this ModID"

Open in new window

It is always better to fix a design flaw than to fight with it for the life of the application.    -- hang that advice on your wall.

Normalize the schema as Dale suggested by creating a separate table to log modifications.  That way you can have 0-infinity rather than ALWAYS 10.  Your search is also simplified and your uniqueness requirement can be handled by the database engine using a unique index rather than having to use VBA code in every form, query, whatever, that can add or update this data.
Thomas ReidMaintenance TechnicianAuthor Commented:
Modifications is one table called tblMods
All parts are stored in another table called tblTooling

Modifications are applied to multiple tools, hence why I have PartAppliedTo1, PartAppliedTo2, etc. This data is selected from tblTooling
However, I was asked to also have fields showing the date that the modification was applied: DateCompleted1, DateCompleted2, etc

Had I not needed the DateCompleted than I would have just used a multivalue combo box, however I was specifically asked for this.

This is why I want to set it so that if a part is selected for PartAppliedTo1, then it can't be selected for any other PartAppliedTo.

If that all makes sense?
A many-to-many relationship is implemented with a junction table.  The junction table includes a FK to tblA and a FK to tblB.  The junction table also might contain intersection data such as a date or a comment, etc. This is what brought you here and if the relationship had been properly structured to begin with, you would have just added a new column to the junction table and been done with it.

You know you have a design flaw when you have to suffix column names with numbers.  Other repeating groups such as Jan, Feb, Mar, Or Sun, Mon, Tue are harder to spot since there is nothing repetitive in the name of the column.

Do a little reading on many-many relationships and junction tables.  I've attached a sample to show you how the relationship is implemented in real life.  The sample app includes two main forms with a subform.  Each main form looks at the relationship from its perspective and the subform shows the junction table and the other side of the relationship.

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

From novice to tech pro — start learning today.