query problem

[barit]

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 previous 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 query 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.MailingListNameID
WHERE (((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

 

[kjv1611]

If you are wanting to do it with a SQL statement, I have 2 suggestions:
1.) Try building the query in query design view, then copy the SQL code from the SQL View window for the working query.

2.) For any problems there, try posting in the forum, Microsoft: Access Queries and Jet SQL, forum701



Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Of course, if you use the first "option" i listed above, then just set the record source for the form equal to the created query or your SQL code.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Of course, if you use the first "option" i listed above, then just set the record source for the form equal to the created query or your SQL code/string.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV