Allowing multiple selections in a list box for mailing list

[antheana]

Hi there,

I am trying to create a tblMailingList, but am a little stuck on multiple selections. The people that have opted into this mailing list will be given a list of different interests they want to be kept informed about e.g. services, special offers, 3rd party services etc.

At any given time, If I create a newsletter for services, I want to run a query to extract everyone who has said they want to be kept up to date about services. I was thinking of creating an interests table and using a lookup field in the tblMailinglist so I can select which interests they have, but it wont allow me to select more than one e.g. services OR special offers.

How can I get around this? I don't want to have a separate field for each interest, I just want this one interests field in tblMailinglist to have all of the interests saved there so I can perform various queries.

Any ideas?

[PEB]

Basiclly U need 3 tables:

1. Table Persons: with ID of the respectiv person, his mail and other details relativ to the person

2. Table Interests That contains all kind of interests and their respectives ID's

3. Interests by people: In this table can be described all interests for one person! And with other words all interests for all people!

So when u need all interested in services u should look in this 3-th table and get those people and get the mails in the table with persons!

Is this helpful?

[NeoPa]

This will give all the data required but the interests will be in separate output records.
This may prove difficult to process in future (2 is easy Min() & Max() but >2 gets v complicated).
It may be worth considering three separate fields in the one record - Interest1; Interest2; Interest3.
A table of all the possible interests is a good idea in either scenario.

[antheana]

Thank you both for your replies.

PEB, I didn't really understand that. Would the 3rd table display each interest and wihtin that each member who has selected that interest? In which case, how do I link the two?

NeoPa - that was what I was thinking of doing, but the problem is that say I end up having 15 interests, if a member was only interested in 1, then it would mean 14 blank records wouldn't it? I was just trying to reduce data redundancy really, but looks like I may just have to have blank records?

Thanks to both of you for your help.

[NeoPa]

Thank you both for your replies.

PEB, I didn't really understand that. Would the 3rd table display each interest and wihtin that each member who has selected that interest? In which case, how do I link the two?

NeoPa - that was what I was thinking of doing, but the problem is that say I end up having 15 interests, if a member was only interested in 1, then it would mean 14 blank records wouldn't it? I was just trying to reduce data redundancy really, but looks like I may just have to have blank records?

Thanks to both of you for your help.

No.
My idea was to populate a single record with multiple 'Interest' fields.
This would involve creating all these fields in your table to start with, and maybe some code in your entry form to move filled items left if not all are filled in. The best way for this is to disable all empty fields except the left-most one OnCurrent, but updating which fields are enabled in the AfterUpdate event.
Let me know if this is still not clear.

[PEB]

If I undertstand well there is proirity of interests or something like this?

U need specify which one is the first interest, which one is the second?

My mind is to create a table with interests like this:

Id, ID_person, ID_interest, Interest_priority
1, John, IT,1
2,John,Theatre,2
3,John,Cinema,3
4,Peter,Foot,1

When this table is populated with info u'll have the interests of al people without fields that are empty! And without seeking in 15 fields to see the wanted interest as it is the idea of NeoPA!

Such construction should be easier to retrieve the needed info and to mail everybody that has the respective interest!

The information about the mail of the respective person is in a separate table with the persons:
Person_ID, First Name, Last Name, Mail
John, John, Atanasov, [e-mail]

Hope that my idea is a bit clear! :)

[antheana]

Hiya, thanks for your replies, unfortunately it's still a bit unclear. There's no need for a priority - it's just that I know how frustrating it is to receive newsletters with things that dont interest me, so want to only send newsletters to those who have opted to hear about certain things e.g. new courses.

So the plan is to create my mailing liste members table, then create an interests table, then create a members interest table - correct me if I'm mistaken??

tblMailingListMembers - contains only contact details for member inc memberID
tblInterests - contains a list of all interests e.g. special offers, new courses etc
tblMembersInterests - contains the memberID (foreign key) and interest (foreign key) and each member can appear in this table more than once if they have an interest in more than one thing?

Am I right? I guess this would normalise it as at the mo I've mistakenly got it down as a one-to-many relationship.

If I'm not right, then I'm changing careers to making teapots or something!

Anthea

[antheana]

OK I obviously didn't read it thoroughly enough...

tblMembersInterests will contain the name and email address of each member and there will be a separate record for them for every interest they have e.g.

Jason; King; jking@hotmail...; new services
Jason; King; jking@hotmail...; new courses
Andrew; Jones; andrewjones@aol...; special offers

So I just need to create a query each time I want to send to a specific mailing list e.g. show me everyone where 'new services' is in the interests field??

Ok, tell me I don't gotta make teapots!!!

[NeoPa]

Your earlier post is probably the better answer.
This is principally what Vladi (PEB) was suggesting. I misread your request a bit and my suggestion was not in line with what you needed.
In short, it doesn't sound like you'll need to worry about those teapots.