I have built an form in which users select are able to find which contacts have attended 2 or more events. The underlying table holding the info looks like:
ContactID EventID
1 1
1 3
2 3
3 1
3 2
4 1
The query i use in query builder works, it is:
INSERT INTO tblMSContactID ( contactID )
SELECT DISTINCT tbllinkEvents.ContactID
FROM tbllinkEvents
WHERE (((tbllinkEvents.ContactID)
IN (SELECT ContactID
FROM tbllinkEvents
WHERE EventsID = 15))
AND ((tbllinkEvents.EventsID)=31));
However when i use enter the query into the back of the form using vb it builds the statement
INSERT INTO tblMSContactIDa ( ContactID )
SELECT DISTINCT tbllinkEvents.ContactID
FROM tbllinkEvents
WHERE (((tbllinkEvents.ContactID) In (SELECT ContactID FROM tbllinkevents WHERE EventsID = 31 AND EventsID = 15)));
and for some reason this query wont work. Ihave tried changing the brackets, but when it builds the query it keeps changing them back 2 the SQL statement that cant work.
Is there any way of getting Vb to build the query correctly? Or is there another statement i can use to select a contacts ID?
Really neeed help!!!
Ross
ContactID EventID
1 1
1 3
2 3
3 1
3 2
4 1
The query i use in query builder works, it is:
INSERT INTO tblMSContactID ( contactID )
SELECT DISTINCT tbllinkEvents.ContactID
FROM tbllinkEvents
WHERE (((tbllinkEvents.ContactID)
IN (SELECT ContactID
FROM tbllinkEvents
WHERE EventsID = 15))
AND ((tbllinkEvents.EventsID)=31));
However when i use enter the query into the back of the form using vb it builds the statement
INSERT INTO tblMSContactIDa ( ContactID )
SELECT DISTINCT tbllinkEvents.ContactID
FROM tbllinkEvents
WHERE (((tbllinkEvents.ContactID) In (SELECT ContactID FROM tbllinkevents WHERE EventsID = 31 AND EventsID = 15)));
and for some reason this query wont work. Ihave tried changing the brackets, but when it builds the query it keeps changing them back 2 the SQL statement that cant work.
Is there any way of getting Vb to build the query correctly? Or is there another statement i can use to select a contacts ID?
Really neeed help!!!
Ross