searching on more than one field

[tigerstripes]

I have a mailing list database with three fields for interested in and want to be able to list anyone who is intrested in say Easter activities for a mailshot. This could have been input in any of the three fields.

I can do this by running a parameter query with three different inputs, one for each field but was wondering if there was a simple piece of code to enable users to just enter the interest once?

I do have a basic grasp of VBA but it is quite limited and I havent been able to work it out and havent been able to find anything doing a search on here so far.

Thanks in advance for any help / suggestions.

 

[neileg]

The problem is your design. Instead of having 3 columns for interests you should have three records in a related table. This way you only have one field to search. This is a concept known as normalisation.

 

[tigerstripes]

Would this still allow a customer to have more than one interest in the database? i.e. be on the mailing list for Christmas mailshots, easter mailshots and historic mailshots

 

[Rabbie]

Yes it would. You would have a one to many relationship between customer and interest. This would allow a customer to have as many interests as they like.

You may find these links useful

http://www.datamodel.org/NormalizationRules.html

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

You should also try googling for "data normalisation".

 

[tigerstripes]

Thanks for that Rabbie, I think most of it makes sense!

I need my customer table and my interest table and basically have a seperate table with customer reference and interest refernce in it so each customer has any amount of interests

Is that about right?

How does this affect data entry though? Does someone need to know customer numbers and interest numbers in order to enter new customer details?

 

[Rabbie]

The easiest way is have a form/subform set up. The Customer info would be in the Form and the Interest data on the subform.

Then the customer Id will be available to be added to the Interest record.

If you base your Subform on a query that has the Customer and interest info then it should all work smoothly.

 

[tigerstripes]

Thanks, will have a go.