Is the table design wrong?

[Guest]

I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite
FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =
tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

 

[Bill Edwards]

tblPerson (PersonId, Surname, GivenName, Street, City, Telephone, etc --
stuff specific to a person)
tblEvent (EventId, EventName, EventDate, etc. -- stuff specific to an event)
tblEventPerson (PersonId, EventId, Attended, Paid, etc., -- To relate the
above two tables in a many to many relationship)

Main form based on tblPerson
Subform based on tblEventPerson

 

[Guest]

Thanks for the reply Bill. Your suggestion is exactly the way I have set up
3 tables but my objective is to have Invite check off form in continuous form
so, user can see all names all at once and just do the check off on invitees
list. User don't want to check off one by one in main form with subform.
Will there be any other way?

 

[Edward Reid]

skk,

Bill's design is what you want. You'll be able to do the forms as you
want, and much more easily than with the design you presented. You will
have many problems if you try to use a two-table design.

Note that there's no "invite" field in the database. You can still have
an "invite" check box on a form. The fact that a person has been
invited is represented by the mere existence of a record in
tblEventPerson.

Personally I'd use the name tblInvitations rather than tblEventPerson.
The latter name is intended (and does) make explicit the fact that it
represents a many-to-many relationship between the other two tables. In
this case, there's more to the invitation than just the fact of its
existence -- for example, you might want the date the invitation was
proffered, whether it was mailed or phoned.

Your "new event" form will need to be based on a left join, so that you
see the uninvited people. I can't give you the exact setup off the top
of my head, but it will work.

I'm a bit confused by your description of the other situation.
Actually, it seems to me that reviewing the list for an event would be
no different from viewing a new event. In either case you might want an
option to show only already-invited or only uninvited persons. And you
probably want a form showing one person and listing all future events,
either to review invitations for a person or to select events for a
newly added person.

Edward