If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query problem - many to many relationship
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
Query Problem | Diana | General Discussion | 3 | June 25th, 2004 07:21 PM |
Access 97 Query problem | Maggic | General Discussion | 4 | June 16th, 2004 10:38 AM |
Query Join Problem | Tom | Running & Setting Up Queries | 0 | May 31st, 2004 09:54 AM |
Query problem | MBison80 | New Users | 2 | May 5th, 2004 06:00 PM |