Adding an Input Field within a Query

[Grace Mah]

I have a database that has two tables. One table (called
Companies)is for company names, addresses and other
relevant data. The other table (called Contacts) is for
individuals within those companies. One of the fields in
this table is a Tes/No field to indicate which
inddividuals are to receive an invitation for an Event. I
also created a relationship between these two tables. I
then created a query that selected all the individuals
that are to receive an invitation to print address
labels. Now I would like to insert an input field in the
Query to indicate RSVP responses to the invitation. Is it
possible to do that to a Query or is there a better way to
do this?

 

[John Vinson]

I have a database that has two tables. One table (called
Companies)is for company names, addresses and other
relevant data. The other table (called Contacts) is for
individuals within those companies. One of the fields in
this table is a Tes/No field to indicate which
inddividuals are to receive an invitation for an Event. I
also created a relationship between these two tables. I
then created a query that selected all the individuals
that are to receive an invitation to print address
labels. Now I would like to insert an input field in the
Query to indicate RSVP responses to the invitation. Is it
possible to do that to a Query or is there a better way to
do this?

Data cannot be "stored" in a Query. A Query is simply a view of data
which is stored in Tables (and *only* in tables). Unless you have an
RSVP field in the Contacts table you will not be able to record that
information. Of course, if you include that field in the table, you
can include it in the query as well.

HOWEVER - assuming that each contact may recieve invitations to
multiple events, and may accept some invitations and decline others,
the Contacts table (or the Events table for that matter) is the WRONG
table to store either the invitation or the RSVP! You have a Many
(contacts) to Many (events) relationship; the proper structure would
have three tables:

Contacts
ContactID
<bio and contact information>

Events
EventID
EventName
EventDate
<other info about the event as a whole>

Invitations
EventID <what are people being invited to>
ContactID <who's invited>
Accepted Yes/No <did they accept the invitation>

There's no need for a yes/no field indicating that they're invited;
just the existance of a record in Invitations defines that.

John W. Vinson[MVP]

 

[gjmah]

-----Original Message-----

Data cannot be "stored" in a Query. A Query is simply a view of data
which is stored in Tables (and *only* in tables). Unless you have an
RSVP field in the Contacts table you will not be able to record that
information. Of course, if you include that field in the table, you
can include it in the query as well.

HOWEVER - assuming that each contact may recieve invitations to
multiple events, and may accept some invitations and decline others,
the Contacts table (or the Events table for that matter) is the WRONG
table to store either the invitation or the RSVP! You have a Many
(contacts) to Many (events) relationship; the proper structure would
have three tables:

Contacts
ContactID
<bio and contact information>

Events
EventID
EventName
EventDate
<other info about the event as a whole>

Invitations
EventID <what are people being invited to>
ContactID <who's invited>
Accepted Yes/No <did they accept the invitation>

There's no need for a yes/no field indicating that they're invited;
just the existance of a record in Invitations defines that.

John W. Vinson[MVP]
.

I modified my Contacts table and removed the Yes/No field
for one Event and created the Events and Invitations table
as per your instructions. I now have an Invitations table
that contains 498 records which contains the EventID,
ContactID and Accepted fields. However, because the
ContactID field only contains numbers, how would I know
who is connected to each record to fill in the Accepted
column?

 

[John Vinson]

I modified my Contacts table and removed the Yes/No field
for one Event and created the Events and Invitations table
as per your instructions. I now have an Invitations table
that contains 498 records which contains the EventID,
ContactID and Accepted fields. However, because the
ContactID field only contains numbers, how would I know
who is connected to each record to fill in the Accepted
column?

By creating a Query joining it to the Contacts table; or by putting it
on a Subform on a form based on the Contacts table; or by using a Form
with a Combo Box bound to the contact ID but displaying the contact
name; or any of a dozen other approaches.

If you're using table or query datasheets to view your data... Well,
don't use table or query datasheets to view your data. They're not
appropriate for that purpose.

John W. Vinson[MVP]