Find records with multiple fields

[damo]

I have created a database which stores lists of documents, that we have archived. There are 25 fields where users can enter what the documents are that we have archived in a certain location. I want the users to be able to perform a find, where if they put in the document's details in the first field it will also search for the same details in the other 24 fields.

Can anyone tell me how this can be done?

Thanks

Damien

[brownees]

Create a script to copy the contents of the first field, add new request, paste contents into second field, add new request, paste contents into third field etc. Finally perform find. This will find any records that have an occurrence of the data in any field.

[LaRetta]

Hi Damien

I hate to break the news to you but someone should say it ... you will continue to have problems working with 25 'like' fields of data. They really should be records instead of fields. Trying to perform a find through them is just a small taste of the kinds of headaches you'll encounter.

If you need assistance splitting them, it can be quite easily with vs. 7. Let us know if you would like help in doing so. Having said that ... yep, multiple find requests OR a concatenated calculation (yikes - 25 fields). It'll be a pain in the booty every time you turn around.

LaRetta

[hayesk]

I agree with LaRetta.

In your current form, it'll make it hard to change anything. What happens if you add a new location? Or change a document from one location to another?

You should have one table with Locations, and another with Documents. With only one field, not 25.

Make two tables and relate them on the Location Field.

[damo]

I think I have been misunderstood, the documents in the 25 fields are the contents of one archive box, the only time they will be moved is when they reach 7 years old, and are destroyed.

I'll give Brownees' suggestion a go.

If it helps, it is uncommon for more than the first half dozen fields to contain information.

[-Queue-]

Then it would be most efficient to have a Boxes table, one record per box, related to the Documents table, which could have up to 25 records per box, and a portal in the Boxes table to add the Document records. An added benefit is only one field needs to be searched.