This problem is a bbit complecated

[Terminus]

This problem is a bbit complecated but i really need help on it!!!

I have a table holds information from a list box of events they have attended eg

ContactID EventAttended
1 3
1 4
2 4

What im trying to do is create a form which will allow me to select people say which went both to event three and four without duplicating there id.

can any1 help?

Terminus

 

[jimb0ne]

Hi Terminus,
I'm not exactly sure what you mean by select people who went to both events 3 and 4 on your form. what will your form be displaying? Will the form be bound to the table with ContactID and EventAttended? Is contactID the primary key in your table, and if so, do you want to be able to have 1 contactID per person, and then have multiple events that they've attended?

James

 

[rdodge]

In your 1st Query/SQL Statement,

SELECT ContactID, SUM(EventAttended) As AllEvents
FROM tblEventsAttended
WHERE EventAttended = 3 Or EventAttended = 4
GROUP BY ContactID
HAVING SUM(EventAttended) = 7

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[CajunCenturion]

You could try something along the lines of:

SELECT DISTINCT ContactID
FROM tblEventsAttended
WHERE EventAttended = 3 AND 
      ContactID IN (SELECT ContactID
                    FROM tblEventsAttended
                    WHERE EventAttended = 4);

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein