How do i make a query to find people who came to an event

[Guest]

I have a list of people with events following the name, etc. with boxes in
each column for each event and i check the box if they have attended the
event.
i want to do a mailing with any of the people who have come to any of these
events.
When i do the query i mark yes under each event, but the query comes back
with only the people who have come to ALL of the evnets I have notated with a
yes.
How can I get a list of all of the people who have come to ANY events?
Thanks

 

[John Vinson]

I have a list of people with events following the name, etc. with boxes in
each column for each event and i check the box if they have attended the
event.

Then you have a spreadsheet, not a properly normalized relational
database. Storing data - events - in column names is Bad Design and
will make your life much more difficult.

A better design would have three tables: People, Events, and
Attendance. The Attendance table would have fields for the unique
PersonID and the EventID, and one record for each person who attended
each event.

i want to do a mailing with any of the people who have come to any of these
events.
When i do the query i mark yes under each event, but the query comes back
with only the people who have come to ALL of the evnets I have notated with a
yes.
How can I get a list of all of the people who have come to ANY events?
Thanks

With your current design, use OR logic: in the query grid put Yes
under each event field, but put them on *different lines* in the grid.
The result will be true if any one of the fields is true.

John W. Vinson[MVP]

 

[Guest]

John,
Thanks so much. This actually worked. I am sure you knew that it would,
but I was shocked. I would love to know how to change this "spreadsheet'
into workabel tables. I have 20 different events across the board on this
table. if htere is somewhere that I can go to get directions on how ot do
this, i would appreciate that.
thank you very much

 

[John Vinson]

John,
Thanks so much. This actually worked. I am sure you knew that it would,
but I was shocked. I would love to know how to change this "spreadsheet'
into workabel tables. I have 20 different events across the board on this
table. if htere is somewhere that I can go to get directions on how ot do
this, i would appreciate that.
thank you very much

A "Normalizing Union Query" can move your data into a proper
structure.

First create the target tables - People, Events, Attendance. You know
better than I what fields you now have and what you will need, but at
minimum I'd suggest:

People
PersonID <Primary Key, autonumber or manually assigned member ID>
LastName
FirstName
<other biographical data as needed>

Events
EventID <Primary Key, autonumber or manual unique ID>
EventDate <don't use Date as a fieldname>
<other info about the event>

Attendance
PersonID
EventID
<any needed info about this person's attendance at this event>

You'll need to manually fill the Events table with the 20 rows for the
twenty events, and note the EventID for each. You can probably run a
simple Append query to append the member information from your
wide-flat table into the People table.

You'll then need to create a UNION query in the SQL window. I don't
know the structure of your table, or whether you have a memberID in
the big table already - I'll assume so, but you can get around the
problem if you don't (post back). Create a new Query, and use View...
SQL. The query will say

SELECT;

Edit this to

SELECT MemberID, (1) AS EventID
FROM wideflattable
WHERE Event1 = True
UNION ALL
SELECT MemberID, (2)
FROM wideflattable
WHERE Event2 = True
UNION ALL
<etc through all 20 events>

The (1) and (2) are the autonumber or manual EventID's for the twenty
events.

Save this query as uniAttendance; then create an Append query based on
it to populate Attendance.

John W. Vinson[MVP]