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.
E.g.
ModID: MODIFICATION-001 (Primary Key)
PartAppliedTo1: Example1
PartAppliedTo2: Example 2
PartAppliedTo3: Example3
PartAppliedTo4: Example 4
PartAppliedTo5: Example 5
Etc...
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.
E.g.
ModID: MODIFICATION-001 (Primary Key)
PartAppliedTo1: Example1
PartAppliedTo2: Example 2
PartAppliedTo3: Example3
PartAppliedTo4: Example 4
PartAppliedTo5: Example 5
Etc...
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.
ASKER
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]![
And if it does not find it in PartAppliedTo1 then it looks for the same value in PartAppliedTo2, 3, 4...etc
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]
Can you extend you query like (assuming your data model)
WHERE ( PartAppliedTo1 = [Forms]![NavigationForm]![
WHERE ( PartAppliedTo1 = [Forms]![NavigationForm]
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:
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"
EndIF
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select <columns>
from <your table>
where (PartAppliedTo1 = <your part name> OR PartAppliedTo2 = <your part name> ..... OR PartAppliedTo10 = <your part name>)