Multiple issues with Database might need new design

[joanne2468]

Hello,

I have a database that I am using for different events to track invitation list and RSVPs.

My tables are:
House: with fields FirstName; LastName; District(PK) Invite; RSVP
Senate: w/fields: FirstName; LastName; District(PK) Invite; RSVP
LocalGvmt: w/fields: FirstName; LastName; ID(PK) Invite; RSVP
Event: w/fields: Date; Location; Caterer; Notes

note - *=all fields

I have a queries:
QryHouse= [House].* + [Event].[Date];
QrySenate=[Senate].* + [Event].[Date];
QryLocalGvmt=[LocalGvmt].*+[Event].[Date];

These queries append to a table = [InvitationList]



I have a form where users can go through the Invitation List and check if someone is to be invited to the event and to track the RSVP. My problem is when new records are added or changes are made in the primary tables [house, senate, localgvmt] I don't know how to update the InvitationList table with the updated info without losing the invite and rsvp info.

Any suggestions? Is my design terrible?

Thank you.

Joanne

[ssanfu]

It doesn't look like you are familar with database normalization concepts.

Go to "Roger's Access Library"
http://www.rogersaccesslibrary.com/f..._topic236.html
and read his tutorials.

"Date" is a reserved word and shouldn't be used for object names.

The data shouldn't be updated (copied) to [InvitationList].
The fields for House, Senate & LocalGvmt look to be the same, so they should be in one table with an additional field to identify the "Branch" of government.
There should be a table for names. Maybe "Officials" or "Politicos".


Could you:
Post the fields from your tables
or post a pic of your relationship window
or post your dB (without data)?

[joanne2468]

I've uploaded the database. I have two tables one for events and one for invitees. I have a query that pulls the two together and I want a form that I can enter who is invited and who has RSVP'd. If I have the form based on the query I cannot enter into it. If I make the query a Make Table I can build the form from the make table and enter the invite and rsvp information. But if I have any changes to the InviteeTable or add another event I have to run the query and my make table will delete and I will lose any invites and rsvps I selected in the make table. Any suggestions???

Thank you.

[ssanfu]

Is this close to what you are trying to do?

[joanne2468]

Is this close to what you are trying to do?

Thank you for taking the time to help me. What I really want is an invitation list (which I have) and then a table for events (which I have). Each time an event is created I want someone to select who they want invited from the invitation list. Then once this is done when they start to receive RSVPs I want them to be able to view the list of invited folks only and enter the RSVP information. The problem is I run a query that will take the event data and the invite list and and it will dump it into a make table. From the make table (through a form) is where they select who is invited to the event. The problem is when they need to change a name or add someone new to the invite list, if I run the query it will delete everything in the make table and start with new information. If they selected people to invite, it is lost when the query runs.

[ssanfu]

Using a make table is definitely not the way to go.

You need (at a minimum) a table of Invitees, a table of events and a junction table to link Invitees to events plus the other data (RSVP,comments).

Microsoft has a Event planner template you might look at
http://office.microsoft.com/en-us/te...i:TC001225345|


I know it will take more development, but I think you could use what I posted as a start. I will look at it again when I get home tonight.