Query problem - many to many relationship

D

Deb Smith

I am having a problem with defining the appropriate query for a form
(Form2). This form is used to create an event specific mailing list. Form 2
is a filtered form that automatically populates with the mailing list names
based on selections made in Form1.

Form 2 should allow the user to input data into a number of fields and to
link the records to a specific event. With my current form and structure, I
can populate Form2 with the mailing list names from Form1 and can link the
mailinglistnames to an event, however, it is not allowing me to create
unique records for multiple events.

If a mailing list name has been selected previously for a different event,
the EventID from the other event shows up in the new record. If the event ID
is changed to reflect the new event, then the mailing list name no longer
relates to the previous event.

I know I am doing something wrong in how I am defining the query for my
form but I am not sure what? I believe that form 2 should be based on my
junction table (EventMailList) but I am not sure how to do this properly.

The following is the SQL statement I am using for form2

SELECT DISTINCTROW MailList.MailingListNameID, MailList.MailingListName1,
MailList.Selected, EventMailList.MailingListNameID, EventMailList.Invited,
EventMailList.NumberInvited, EventMailList.LabelRequired,
EventMailList.[Event ID] FROM MailList LEFT JOIN EventMailList ON
MailList.MailingListNameID = EventMailList.MailingListNameIDWHERE
(((MailList.Selected)=Yes) AND ((EventMailList.[Event ID]) Is Null)) OR
(((MailList.Selected)=Yes) AND ((EventMailList.[Event ID])=0)) OR
(((EventMailList.[Event ID])=[Forms]![Event Planning]![Event ID])) OR
(((MailList.Selected)=Yes));

The following is part of the table structure used in this data base

Table 1 - MailList

-MailingListNameID (PK - autonumber)

-MailingListName1 (txt field)

-Selected (Yes/No)



Table 2 - EventMailList

- MailingListNameID(FK - linked to tblMailList.MailingListNameID)

- Event ID (FK-linked to tblEventInfo.Event ID)

- Invited (Yes/No)

- NumberInvited (Number)

- Label Required (Y/N)



Table 3 - Event Info

-Event ID (PK - autonumber)

-Event Date (date)

- OccassionID (FK linked to tblOccassion.OccassionID)

etc

Can someone please provide a suggestion on changes required, what I am doing
wrong or point me in a direction to at least begin to resolve thes issues.

Thanks in advance for the help and suggestions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top