OptionGroups

[Stephanie Sollow]

Short question, long preface:

I have 5 option groups on the same form, and each has 8
options. It's a workshop with several sessions, so Session
A has 8, and the first option's value is 1, etc. (it
itself is a subform to another that contains the
registrants info).

What I would like it to do is either notify or indicate
when the particular session has reached its max of 30
participants. I've got various parts of it to work (such
as the notify part), but I can't seem to it get to count
the number of "true" instances for each option in each
option group... Currently I have 5 queries for each
Session (A - E), tied to a command button that when
clicked prompts for the correct session (1-8) (e.g.,
clicking button and entering 1 displays a datasheet for
each person registering for Session A1). So, I run the
same query 8 times per session... ugh! It was a kludge...

Anyway, what I want to be able to do, in pseudo code: if
count of [Option162].OptionValue=1 is = 30 then display
msg. (I haven't named the options yet, but Option162 would
correspond to Session A1). I've tried all sorts of ways to
make that logic work; so much so my head is spinning. And
short of adding 40 tiny subforms that display the results
of the query... (which would work, but be inefficient) ...
we don't care for this info, who signed up, just that as
we add new registrations, we will know when each fills up.

I'm just wondering if even counting the number of true
instances for each OptionValue is doable...

Thanks

 

[Pavel Romashkin]

I'd like to know how are you storing the session information in the database?
Assuming that your design is normalized, all you need to do is to run a
GROUP BY query on your data, and select the sum of registrations:

SELECT First(Session_ID) FROM myTable GROUP BY Session_ID HAVING
Sum(SessionRegistration) >= 30

Cheers,
Pavel

 

[Stephanie Sollow]

Patel,

Thanks. I have a table set up with following fields/type:

RecordID AutoNumber
RegistrantID Number <-- tied in with the
participant info table
SessionA Text <-- is this a problem, s/b number?
SessionB Text
SessionC Text
SessionD Text
SessionE Text

Each matches up with the option groups.... so that
(stating the obvious maybe) session A data (1 - 8) is
in the Session A field, etc.

There's no existing data in the table other than test
data, so restructuring is an option if need be.

Thanks again,

Steph

-----Original Message-----
I'd like to know how are you storing the session information in the database?
Assuming that your design is normalized, all you need to do is to run a
GROUP BY query on your data, and select the sum of registrations:

SELECT First(Session_ID) FROM myTable GROUP BY Session_ID HAVING
Sum(SessionRegistration) >= 30

Cheers,
Pavel

Short question, long preface:

I have 5 option groups on the same form, and each has 8
options. It's a workshop with several sessions, so Session
A has 8, and the first option's value is 1, etc. (it
itself is a subform to another that contains the
registrants info).

What I would like it to do is either notify or indicate
when the particular session has reached its max of 30
participants. I've got various parts of it to work (such
as the notify part), but I can't seem to it get to count
the number of "true" instances for each option in each
option group... Currently I have 5 queries for each
Session (A - E), tied to a command button that when
clicked prompts for the correct session (1-8) (e.g.,
clicking button and entering 1 displays a datasheet for
each person registering for Session A1). So, I run the
same query 8 times per session... ugh! It was a kludge...

Anyway, what I want to be able to do, in pseudo code: if
count of [Option162].OptionValue=1 is = 30 then display
msg. (I haven't named the options yet, but Option162 would
correspond to Session A1). I've tried all sorts of ways to
make that logic work; so much so my head is spinning. And
short of adding 40 tiny subforms that display the results
of the query... (which would work, but be inefficient) ...
we don't care for this info, who signed up, just that as
we add new registrations, we will know when each fills up.

I'm just wondering if even counting the number of true
instances for each OptionValue is doable...

Thanks

.

 

[Pavel Romashkin]

What you have is a spreadsheet design - it seems intuitive but is poorly
suited for searching and querying. Such design is not optimal because
related information, such as Sessions, is stored in fields instead of
records. It does not allow easy addition of a sessions or session
options and makes querying very difficult.
I admit, it lends itself well to the form design you have, but its about
the only upside.

I'd go with

MyTable:
RecordID, AutoNumber
RegistrantID, FK from the participant info table
SessionID Number, FK from the Sessions table
SessionOption, <-- this is from your form, 1-8

SessionsTable
SessionID, autonumber
SessionName, a descriptive name

In this case the query I proposed first will work perfectly:

SELECT First(SessionID), First(SessionOption) FROM myTable GROUP BY
SessionID, SessionOption HAVING Count(SessionOption) >= 30

In the design you have now, you will have to use 6 separate queries
becasue you have to query 6 fields independently:

qDefA -> SELECT Count(SessionA) FROM MyTable GROUP BY SessionA
qDefB -> SELECT Count(SessionB) FROM MyTable GROUP BY SessionB
.. . . . . .

etc. and look at the counts.
I would redesign the database while you can - it will save the pain of
not being able to use it properly in the future. You can try running the
Access optimizer on your design to see what it will propose.
Good luck,
Pavel

Patel,

Thanks. I have a table set up with following fields/type:

RecordID AutoNumber
RegistrantID Number <-- tied in with the
participant info table
SessionA Text <-- is this a problem, s/b number?
SessionB Text
SessionC Text
SessionD Text
SessionE Text

Each matches up with the option groups.... so that
(stating the obvious maybe) session A data (1 - 8) is
in the Session A field, etc.

There's no existing data in the table other than test
data, so restructuring is an option if need be.

Thanks again,

Steph

-----Original Message-----
I'd like to know how are you storing the session information in the database?
Assuming that your design is normalized, all you need to do is to run a
GROUP BY query on your data, and select the sum of registrations:

SELECT First(Session_ID) FROM myTable GROUP BY Session_ID HAVING
Sum(SessionRegistration) >= 30

Cheers,
Pavel

Short question, long preface:

I have 5 option groups on the same form, and each has 8
options. It's a workshop with several sessions, so Session
A has 8, and the first option's value is 1, etc. (it
itself is a subform to another that contains the
registrants info).

What I would like it to do is either notify or indicate
when the particular session has reached its max of 30
participants. I've got various parts of it to work (such
as the notify part), but I can't seem to it get to count
the number of "true" instances for each option in each
option group... Currently I have 5 queries for each
Session (A - E), tied to a command button that when
clicked prompts for the correct session (1-8) (e.g.,
clicking button and entering 1 displays a datasheet for
each person registering for Session A1). So, I run the
same query 8 times per session... ugh! It was a kludge...

Anyway, what I want to be able to do, in pseudo code: if
count of [Option162].OptionValue=1 is = 30 then display
msg. (I haven't named the options yet, but Option162 would
correspond to Session A1). I've tried all sorts of ways to
make that logic work; so much so my head is spinning. And
short of adding 40 tiny subforms that display the results
of the query... (which would work, but be inefficient) ...
we don't care for this info, who signed up, just that as
we add new registrations, we will know when each fills up.

I'm just wondering if even counting the number of true
instances for each OptionValue is doable...

Thanks

.

 

[Stephanie Sollow]

Pavel,

Thanks again. The initial db was set up ad hoc a few years
ago and I just keep reusing since I was okay with the
quirks, knowing at some point I'd have to actually plan it
out. That "some point" is now, since someone else will be
doing the data entry...

Anyway, thanks again.

Steph

PS... sorry for calling you Patel in my previous reply.

-----Original Message-----
What you have is a spreadsheet design - it seems intuitive but is poorly
suited for searching and querying. Such design is not optimal because
related information, such as Sessions, is stored in fields instead of
records. It does not allow easy addition of a sessions or session
options and makes querying very difficult.
I admit, it lends itself well to the form design you have, but its about
the only upside.

I'd go with

MyTable:
RecordID, AutoNumber
RegistrantID, FK from the participant info table
SessionID Number, FK from the Sessions table
SessionOption, <-- this is from your form, 1-8

SessionsTable
SessionID, autonumber
SessionName, a descriptive name

In this case the query I proposed first will work perfectly:

SELECT First(SessionID), First(SessionOption) FROM myTable GROUP BY
SessionID, SessionOption HAVING Count(SessionOption) >= 30

In the design you have now, you will have to use 6 separate queries
becasue you have to query 6 fields independently:

qDefA -> SELECT Count(SessionA) FROM MyTable GROUP BY SessionA
qDefB -> SELECT Count(SessionB) FROM MyTable GROUP BY SessionB
.. . . . . .

etc. and look at the counts.
I would redesign the database while you can - it will save the pain of
not being able to use it properly in the future. You can try running the
Access optimizer on your design to see what it will propose.
Good luck,
Pavel

Patel,

Thanks. I have a table set up with following fields/type:

RecordID AutoNumber
RegistrantID Number <-- tied in with the
participant info table
SessionA Text <-- is this a problem, s/b number?
SessionB Text
SessionC Text
SessionD Text
SessionE Text

Each matches up with the option groups.... so that
(stating the obvious maybe) session A data (1 - 8) is
in the Session A field, etc.

There's no existing data in the table other than test
data, so restructuring is an option if need be.

Thanks again,

Steph

-----Original Message-----
I'd like to know how are you storing the session information in the database?
Assuming that your design is normalized, all you need

to
do is to run a

GROUP BY query on your data, and select the sum of registrations:

SELECT First(Session_ID) FROM myTable GROUP BY

Session_ID
HAVING

Sum(SessionRegistration) >= 30

Cheers,
Pavel

Stephanie Sollow wrote:

Short question, long preface:

I have 5 option groups on the same form, and each has 8
options. It's a workshop with several sessions, so Session
A has 8, and the first option's value is 1, etc. (it
itself is a subform to another that contains the
registrants info).

What I would like it to do is either notify or indicate
when the particular session has reached its max of 30
participants. I've got various parts of it to work (such
as the notify part), but I can't seem to it get to count
the number of "true" instances for each option in each
option group... Currently I have 5 queries for each
Session (A - E), tied to a command button that when
clicked prompts for the correct session (1-8) (e.g.,
clicking button and entering 1 displays a datasheet for
each person registering for Session A1). So, I run the
same query 8 times per session... ugh! It was a kludge...

Anyway, what I want to be able to do, in pseudo code: if
count of [Option162].OptionValue=1 is = 30 then display
msg. (I haven't named the options yet, but Option162 would
correspond to Session A1). I've tried all sorts of

ways
to

make that logic work; so much so my head is spinning. And
short of adding 40 tiny subforms that display the results
of the query... (which would work, but be inefficient) ...
we don't care for this info, who signed up, just that as
we add new registrations, we will know when each

fills
up.

I'm just wondering if even counting the number of true
instances for each OptionValue is doable...

Thanks
.

.