Searching on multiple fields

[Allen Tuten]

I'm trying to create a report that needs to search on multiple fields in the same record and report when there is a match in any of the multiple fields.

The database consists of a single table which lists compositions and other related information. Each composition is one record (the title is one field). Each composition has a unique page number assigned to it (another field-numeric). There are about 700 compositions.

Each composition can have up to four authors. There are four separate fields for authors (i.e. author_1, author_2, author_3, author_4). The authors are listed by last name.

The same author can be listed in a different field for different compositions. For instance, the author named "Red" might be listed in the author_1 field for one composition, in the author_2 field for another composition, and in the author_3 field for yet another. Not all of the author fields are filled for each composition (some are blank when there are less than 4 authors), although they are always filled starting with author_1, then author_2, etc.

I need to be able to generate a report that searches across all four author fields of each record for all instances of each author's name and then returns the page number of each composition in which the author's name is listed.

For instance, assume the following data is in the page number, author_1, author_2, author_3, and author_4 fields.

Record 1 - page 24, Red, Brown, Yellow, (blank)
Record 2 - page 36, Brown, (blank), (blank), (blank)
Record 3 - page 40, Green, Red, (blank), (blank)
Record 4 - page 55, Red, Green, Yellow, (blank)

I'd like to create a report that shows:
Red - 24, 40, 55
Brown - 24, 36
Yellow - 24, 55
Green - 40, 55

I can get close to the results I need using a group break on the author's name field in a report, but I've only been able to figure out how to do that on a single field (i.e. author 1) at a time. A query also comes close, but I can only see how to do that one author's name at a time. As there are about 400 unique authors, I'd like to find another way to approach the report.

I expect that there's a way to do this, but I just have found it yet! Thanks for any suggestions.

Allen

[Tom Cone Jr]

Re: Searching on multiple fields

I fear the difficulty you're having results from the table structure. This you have probably already figured out, right? The best long term solution is to normalize your data by moving the "repeating" author fields out of the current table and putting them into a new table. That table's record structure would contain only two fields: the unique composition / page number, and the author's name.

Then you'd define a two table set and base your report on the set.

[Allen Tuten]

Re: Searching on multiple fields

Tom,

Thank you for your reply and your offer to help. I've been experimenting with a test database with a separate table for the authors and the report now works correctly. (My database design skills are limited, at best.)

However, the way I have the database currently set up results in one record in the "author" table for each occurence of that author's name.

For instance, using my original examples, the author table has a record for Red - page 24, another for Red - page 40, and another for Red - page 55. That will work for my purposes, but is there any way to have a single record for each author? That would simplify correcting any errors in an author's name.

Thanks again for your help!

[Tom Cone Jr]

Re: Searching on multiple fields

Allen what you're bumping up against is the classic many-to-many scenario. A composition can have many authors. An author can have many compositions. Search for message board threads discussing this, and study the "Students and Classes" template database that ships with Alpha Five.

The traditional solution is to use two 3 table sets.

Compositions, linked one to many to an intermediate table, which in turn is linked one to one with authors.

and

Authors, linked one to many to the same intermediate table, which in turn is linked one to one with compositions.

The intermediate table contains fields that contain link values for both compositions and author tables. i.e. each record in the intermediate table would contain a composition id and an author_id for each composition/author pair in your system. In the first set mentioned above Compositions would be linked to the intermemdiate table through the Composition_id field, and then the intermediate table would be linked (1 to 1) to the authors table using the author_id field.

Hope this helps.

[Allen Tuten]

Re: Searching on multiple fields

Tom,

Thank you for pointing me in the right direction. While reading some other questions, I realized that what I was asking to do was probably more complicated than I expected.

This database will just be used one time with static data - no records will be added or removed - to generate indexes for the book. So, I'll likely just use the two-table set as you first described. I think it will work fine.

Thanks again for your help!

Allen

[Tom Cone Jr]

Re: Searching on multiple fields

You're welcome.

If you're retyping the data in the second table you might want to try using 4 append operations instead. Use a separate append operation for each of the 4 author fields. Then a 5th operation to delete any records that have blank author fields. Good luck with your project.