Stumped on Query Design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am stumped on a query that I need.

TableEvents
eventname
and other data about the event

TableEventsAttended
employee
eventname (linked to TableEvents)
date
etc.

Situation
10 events in TableEvents
Joe attended 7 of them

I need a list of the 3 events Joe did not attend.

It seems simple enough, but I cannot seem to create the correct set-up. I
am not terribly versed in SQL. I tend to create my queries in the design
view, so any hand-holding you can offer would be appreciated.

I am using Access 2003.
 
Shoelaces:

I write in queries mostly also

what you'd have to do is double click on the link from each table (that
links event name) .
select the Option that indicates: Include ALL records from 'TableEvents'
and only those records from 'TableEventsAttended' where the joined fields are
equal.
pull down all the fields or the fields that you want to view from
TableEvents, and pull down Eventname from 'TableEventsAttended'
In the criteria, write Is null
And then you'll have the 3 events
 
Hi Shoelaces,

Try this using a sub query.

SELECT TableEvents.eventname
FROM TableEvents
WHERE (((TableEvents.eventname) Not In (select eventname from
TableEventsAttended where employee = 'Joe')));
 
Try this using a sub query.

I don't really know what a sub-query is, but I followed your code,
substituted the actual field names, etc. and was very pleasantly surprised
that I had what I have struggled with mightily for a long time.

Thank you very much!
 
I am feeling adventurous, but am stumbling.

I am able to do what I wanted to do. Now, I would like to modify this just
a bit.

A person can attend an event. Some persons can organize or present the
event. When I create the list of events the person still needs to attend
(that was the original question), I am returned all the events he did not
attend.

In that list, however, may be events he organized or presented. We assume
that if he organized or presented, he does not need to attend them.

So, the new struggle is to design this query to return those events for
which he still needs to attend AND that he has neither organized or presented.

The SQL I currently have is this:

SELECT Events.EventName, Events.Organizer, Events.Presenter, Events.State
FROM Events
WHERE (((Events.EventName) Not In (select EventName from
Attended where Attendee =[Attendee?])) AND ((Events.State)="[Which State?]"))
ORDER BY Events.EventDate;

How do I restrict in this (sub-form?) the events? I tried a couple syntax
changes and Access balked. No surprise, I suppose.
 
How about

SELECT Events.EventName, Events.Organizer, Events.Presenter, Events.State
FROM Events
WHERE Events.EventName
Not In (select EventName from Attended where Attendee =[Attendee?])
AND Events.Organizer <> [Attendee?]
AND Events.Presenter <> [Attendee?]
ORDER BY Events.EventDate;

The only problem I see with this is that your table structure only
accomodates one Organizer and one Presenter. If that is all you are ever
going to need, then this query should do the job.

HTH
Dale
 
Dale Fye said:
How about
AND Events.Organizer <> [Attendee?]
AND Events.Presenter <> [Attendee?]
Click to expand...

Absolutely wonderful! Thank you.
The only problem I see with this is that your table structure only
accomodates one Organizer and one Presenter. If that is all you are ever
going to need, then this query should do the job.
Click to expand...

For this, there ever could only be one of each.

Thank you sincerely for your help.
 
Back
Top