Class Count

[Drew Shriner]

I'm managing a database for a conference that is to start
next week, and we have multiple sessions that each person
can register for, but each session is limited to 30
attendees maximum. Here is our structure so far:

[Attendee_Information]
RegID PK
first_name
last_name
etc

[Session_ChildObjects]
Session_ChildObjectID PK
Session_ParentObjectID
ListItem

[Session_Registrations]
Session_Registration_ID PK
RegID
Session (where "Session" is a lookup to the
above "ListItem")

Each attendee, known by their attendee number (RegID),
registers for whatever sessions needed. I have generated
reports and forms for displaying a list of people
registered in each session. I need to be able to see how
many people are currently enrolled in each session,
without manually counting how many people are in the list.
What would be the best way to do this?

Wouldn't the best way be to count how many occurances
there are in [Session_Registrations]![Session] for each
session?

I don't know all that much about this particular thing,
even though I've worked (infrequently) with Access for a
year or so now.

I apologize if I wasn't clear enough...

 

[tina]

-----Original Message-----
I'm managing a database for a conference that is to start
next week, and we have multiple sessions that each person
can register for, but each session is limited to 30
attendees maximum. Here is our structure so far:

[Attendee_Information]
RegID PK
first_name
last_name
etc

[Session_ChildObjects]
Session_ChildObjectID PK
Session_ParentObjectID
ListItem

[Session_Registrations]
Session_Registration_ID PK
RegID
Session (where "Session" is a lookup to the
above "ListItem")

Each attendee, known by their attendee number (RegID),
registers for whatever sessions needed. I have generated
reports and forms for displaying a list of people
registered in each session. I need to be able to see how
many people are currently enrolled in each session,
without manually counting how many people are in the list.
What would be the best way to do this?

Wouldn't the best way be to count how many occurances
there are in [Session_Registrations]![Session] for each
session?

I don't know all that much about this particular thing,
even though I've worked (infrequently) with Access for a
year or so now.

I apologize if I wasn't clear enough...
.

sounds like you've described a typical many-to-many
relationship, almost correctly expressed through a linking
table (Session_Registrations).
but your Sessions table is not a child.
what you have are really two parent tables, Attendees and
Sessions, with a many-to-many relationship. table
Session_Registrations serves as the one-to-many child
table to each parent, which links the parents indirectly
(and correctly).

to generate a "total registrants per session" value in a
report based on the Session_Registrations table, here's
one way:
group the records by Session.
in the Session section's footer (or header, what suits
you), set an unbound control's RecordSource property as
=Count([Session])
doing it in a form can be trickier, but probably do-able
if you fiddle with it long enough.

hope this helps.

 

[Casey]

Drew,

What you need to do is create a query with the source
table you mentioned as the basis. Place the Session field
in the first column of the query. Place the Registration
field in the second column of the query. At the top of
the query design screen you will see a symbol that looks
something like an E but is sort of funny looking. When you
put your cursor over this symbol it will give you a
description message saying "Totals". Anyway, click on
this symbol that looks something like an E. In your
column that has the Session field in it, in the Total
criteria make sure "Group By" is iterated. In the column
that has Registration in it, in the Total criteria
choose "Count". This should count the registrations for
each session that you have. Save the query and open it to
see your data. You can also build a form using the form
wizard to present the data, when using the wizard just
choose the query name of your new query and it will use it
as its source.

That should do what you wanted.

I hope I helped.

Have a nice day.

Casey

 

[Drew Shriner]

Yes, it is a typical many-to-many relationship...
the name [Session_ChildObjects] is not from that
relationship... there is a table named
[Session_ParentObjects] also. I most likely have the
terminology wrong, and could do this a better way, but
what I did was I created a record in
[Session_ParentObjects] for every time slot available.
Then, in [Session_ChildObjects], I created a record for
every class available, and I used a lookup between
[Session_ChildObjects] and [Session_ParentObjects], using
the [Session_ParentObjects]![Session_ParentObjectID] Key.
That way it was easy to tell which class was happening
during which time frame. Any suggestions on what I should
have done instead?

And yes, I was about to get the count correct, using the
same method that you gave me... I just stayed up a while
longer and managed to figure it out on my own.

Thanks for the help!

Drew