Counting occurances of same data in different fields

[Guest]

I am creating a conference registration program that allows registrants to
select up to three events to participate in. Is there an easy way to count
the total number of registations for each event that occurs in the three
selection fields?

 

[storrboy]

I am creating a conference registration program that allows registrants to
select up to three events to participate in. Is there an easy way to count
the total number of registations for each event that occurs in the three
selection fields?

I gather you have a 3 fields plus a registrant id in a table?
Would it not be easier to list the registrant 3 times and have one
field for an event? You may find queries and rule processing (limit to
3 events) easier to manage.

 

[Todos Menos [MSFT]]

using Access Data Projects; it is possible to use a trigger to prevent
more than 3 from being entered.

if you implement this logic using Access VBA they can always shut off
the macros for 5 minutes; go and enter a record and then it will take
you a month to diagnose the problem


-Todos

 

[Tony Toews [MVP]]

Todos Menos is not a Microsoft employee. This posting is by A a r o n
K e m p f.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Actually I have a table that has all of the registrants' information
including three fields in which to enter their event choices. A number of
reports are generated from this table, so I do not want multiple participant
IDs in this table. I guess I could create an additional table as you
suggested, but this would complicate all of the existing reports. My
thinking was to create a query that would append all Not Null entries in each
of the three field into a single field (which could be in a totally separate
table). Every time I try this I get a error to the effect "Multiple fields
to same destination" or something similar. Is there a way to get around this?

 

[storrboy]

If the registrant is in the table only once, then a Count() of each
event field should give you the numbers. Or am I not grasping what you
are doing?

 

[Guest]

A count of each event field only gives the total number of registrations. I
want a count of the total number of registrations for each event regardless
of whether the registrant made it their first, second, or third choice. For
example:

ID Event Choice 1 Event Choice 2 Event Choice 3
1 A B C
2 B C A
3 C B D
4 A D C, etc.

How many total As, Bs, Cs, & Ds?

 

[storrboy]

I see what you mean.
Hence why I think the other table setup would have been easier.
I'll ponder it, but it may require doing something in code instead of
just one or two queries.

 

[John Nurick]

Something like this, perhaps:

SELECT EventChoice, COUNT(EventChoice)
FROM (
SELECT [Event Choice 1] AS EventChoice
FROM MyTable
UNION ALL
SELECT [Event Choice 2] AS EventChoice
FROM MyTable
UNION ALL
SELECT [Event Choice 3] AS EventChoice
FROM MyTable
)
GROUP BY EventChoice
ORDER BY EventChoice
;

 

[Guest]

Perfect! Thanks, John!

Something like this, perhaps:

SELECT EventChoice, COUNT(EventChoice)
FROM (
SELECT [Event Choice 1] AS EventChoice
FROM MyTable
UNION ALL
SELECT [Event Choice 2] AS EventChoice
FROM MyTable
UNION ALL
SELECT [Event Choice 3] AS EventChoice
FROM MyTable
)
GROUP BY EventChoice
ORDER BY EventChoice
;

A count of each event field only gives the total number of registrations. I
want a count of the total number of registrations for each event regardless
of whether the registrant made it their first, second, or third choice. For
example:

ID Event Choice 1 Event Choice 2 Event Choice 3
1 A B C
2 B C A
3 C B D
4 A D C, etc.

How many total As, Bs, Cs, & Ds?

 

[Guest]

Thanks for your input, storrboy. John Nurick nailed the solution with the
code in his post.

 

[storrboy]

Thanks for your input, storrboy. John Nurick nailed the solution with the
code in his post.

He's pretty good that way. ' -)