Query to check participation

T

Tay J.

I need to have my query look at the number of times a participant has attend
an event and if less than a set number (ie 2 times) it removes them from a
participant list yet keeps thier name in the database.

As always all help is appreciated.

Tay
 
O

Ofer Cohen

Keep their name where?
Is that a seperate table where the names are stored in?
What do you mean by removing from the participant list, delete from the table?

Consider maintaining a Status field (Valid or Not) that way you wont have to
delete the record, you'll always have the history of events to refer to.
 
L

Larry Linson

Tay J. said:
I need to have my query look at the number of times a participant has
attend
an event and if less than a set number (ie 2 times) it removes them from
a
participant list yet keeps thier name in the database.

As always all help is appreciated.
Click to expand...

What you want to do is likely possible; if you described what data you have
and how you have it stored, it's also possible that someone might be able to
offer helpful suggestions.

Larry Linson
Microsoft Access MVP
 
T

Tay J.

Here is what I have. I have a list of participants for a gymkhana (equestrian
event) and we have a data base with all the participants in it. Some
participants have not shown up for several gymkhanas yet my database will
always add there name when I print the participant list. I would like to have
the database not print those names of participants who have not participated
in the last several gymkhanas.
Is there a way to filter what names are printed to not include the names of
participants who have not participated in the last several gymkhanas?

Tay
 
J

John W. Vinson

Here is what I have. I have a list of participants for a gymkhana (equestrian
event) and we have a data base with all the participants in it. Some
participants have not shown up for several gymkhanas yet my database will
always add there name when I print the participant list. I would like to have
the database not print those names of participants who have not participated
in the last several gymkhanas.
Is there a way to filter what names are printed to not include the names of
participants who have not participated in the last several gymkhanas?
Click to expand...

Does your database contain a table listing which events each member has
attended? I would expect at least three tables: Participants, Gymkhanas
(listing the date and venue of the event), and Participation. If you have
this, you can create a query using a NOT IN () criterion to select
participants who were Not In a selected set of gymkhanas - chosen by date.

What's the structure of your tables? We can help design the query if we can
see what we're working with!

John W. Vinson [MVP]
 
T

Tay J.

First there are several events but we count that as one gymkhana. I have two
tables, one with their personal information and the other to record the date
they attended the event. The latter also serves as information on
participation.

Can I still use the NOT IN () criterion with two tables?

Also how can I send this to you so you can see it first hand if necessary.

Tay
 
J

John W. Vinson

First there are several events but we count that as one gymkhana. I have two
tables, one with their personal information and the other to record the date
they attended the event. The latter also serves as information on
participation.
Click to expand...

What are the relevant fields in this second table? Surely each event can have
several participants, and each person may participate in several events? It
sounds like you need THREE tables not two, or else you're storing repeated
data about the event!

Can I still use the NOT IN () criterion with two tables?
Yes.

Also how can I send this to you so you can see it first hand if necessary.
Click to expand...

Sorry, that would be beyond what I'm comfortable doing as an unpaid volunteer
here.

John W. Vinson [MVP]
 
Top