Finding six items anywhere in six fields

[MinnesotaHam]

I need to search for 4-8 items that will appear in 4-8 fields, but may appear in any order. Some of the items may be repeats of each other, or they may be unique. The DB is a flat file listing of the vacuum tubes used in antique radios. The DB has a field for the count of tubes, then eight fields for the tube names used. If it is a 4 tube radio, the tube count field will have an entry of 4, then only the first four tube fields will be filled in with data for the tube names. The tube names are alphanumeric but for simplicity I'll use numerals only.

Example. I have a five tube radio using these tubes:

47, 47, 49, 50, 51. I want to find only entries that have five tubes, having these tube names , but they can appear in any order. I want to search the DB with example records shown below. Using above search criteria, it should find three records.

Example records

Tube count, tubename1, tubename2, tubename3, tubename4

5, 47, 47, 49, 50, 51

5, 49, 50, 51, 47, 47

5, 47, 49, 47, 50, 51

5, 46, 47, 49, 50, 51

6, 47, 47, 49, 50, 51, 51

Right now, I'm doing searches and sorts to find entries and narrow them down to a list that matches my criteria. My DB is flat but I have some experience with going relational. The DB currently has 5000 plus entries.

How can I do this more efficiently?

[comment]

Does the order in which the tubes are recorded in the 8 fields have any significance?

[MinnesotaHam]

The order in which they are recorded does not matter.

[comment]

I think it would be best to split the tubes off to a related table. The situation here is complicated by the significance of duplicates (i.e. {1;2;3;4} being different from {1;2;2;3;4}). Therefore duplicates need to be eliminated and replaced by a quantity attribute, so that in your example you could search for radios that have related tubes:

2 x 47

1 x 49

1 x 50

1 x 51

and a tube count of 5.

Another option would be to sort the tubes in each record - but that wouldn't be any easier (esp. with version 7), and having a properly normalized structure will have additional benefits when you add other features to your solution.

[normanicus]

Put all the records in one table. Create a field something like 'Category'. Then you can view the records in a portal filtered by the category but the finds will take place on all the records.