• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 42
  • Last Modified:

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 Reid
Thomas Reid
  • 3
  • 3
  • 2
  • +1
1 Solution
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] ..)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 FyeCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now