Query one table with multiple element

[MahiMahi]

I ahve a column that is labeled Subdicipline. Inside of this column I have individual subs for example Electo-Optical, Materials, and Radar. Know i am trying to build a query that will return the results when all three are entered into a single field. Here is the catch they can be in random order.

SELECT CombinedElements.FullName, CombinedElements.Subdiscipline, CombinedElements.DataType, CombinedElements.Associations, CombinedElements.Parent
FROM CombinedElements
WHERE (((CombinedElements.Subdiscipline)="Materials, Electro-Optical, Radar"));

This is my code as it stands know, but it will only return the values when they are in the same order as the query. Any help.

 

[OhioSteve]

I assume that the users can enter phrases into CombinedElements.Subdiscipline. So you are seeking records that contain references to these subdisciplines. It looks like each record in CombinedElements refers to one student. If those assumptions are correct, then you need to rethink your schema. You may need a child table with a structure like this~

StudentSubdisciplines
SSD_ID (autonumber)(pk)
studentID (integer) (fk)
SSD (text)

The parent table's structure would be~
CombinedElements
studentID (integer) (pk)
[other fields]

You also may need a lookup table like this~

validStudentSubdisciplines
SSD_Name (text) (pk)

 

[MahiMahi]

Well actually this subdiscipline is part of a individual table. Let me explain a bit more. You may have one element that may be used in several different sub-disciplines ie a common element. However, when the user inputs this they can do so in a variety of orders what i need to do is search for the terms that are in the subdiscipline field that lets say are 1 2 4 5 I need to be able to return all instances of
23451
34512
12453
etc

 

[PHV]

Have you tried something like this ?
WHERE Subdiscipline LIKE "*Materials*"
AND Subdiscipline LIKE "*Electro-Optical*"
AND Subdiscipline LIKE "*Radar*";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

PHV's answer is a good short term fix. However, in the long term you need to normalize the data. Otherwise, you may continue to experience problems.