Query to check participation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tay J. - 18 Jun 2007 03:14 GMT

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

Reply to this Message

Ofer Cohen - 18 Jun 2007 07:05 GMT

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.

Signature

Good Luck
BS"D

> 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
[quoted text clipped - 3 lines]
>
> Tay

Reply to this Message

Tay J. - 20 Jun 2007 03:59 GMT

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

> Keep their name where?
> Is that a seperate table where the names are stored in?
[quoted text clipped - 10 lines]
> >
> > Tay

Reply to this Message

John W. Vinson - 20 Jun 2007 04:16 GMT

>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
[quoted text clipped - 4 lines]
>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?

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]

Reply to this Message

Tay J. - 20 Jun 2007 13:43 GMT

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

> >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
[quoted text clipped - 15 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 20 Jun 2007 17:55 GMT

>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.

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.

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

John W. Vinson [MVP]

Reply to this Message

Larry Linson - 18 Jun 2007 07:09 GMT

>I need to have my query look at the number of times a participant has
>attend
[quoted text clipped - 3 lines]
>
> As always all help is appreciated.

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

Reply to this Message