Event Management Database Form

[kattz]

I only have Access 97 with no wizards and no add ins and do not have access to the templates so i can't even play around with them.

I am trying to create a database which will have:
Client Name
Contact details within that client (could be more than one)
Functions that can be invited to
RSVPs to the functions

I have started this already with a one to many relationship and a contact table/sub form within the client table/form.

CLIENT TABLE FIELDS
ClientID
ClientName
StreetAddress
etc

CONTACT TABLE FIELDS
ContactID
ClientID (link)
FirstName
LastName
etc
Xmas Party-yes/no
golf day - yes/no

Now I have a FunctionTble and created a form based on this table and the subform to this will be a list of of all the contacts who have been invited to this event with their Company name.

FUNCTION TABLE
FunctionID
Type of Function
Function Date
Function Location
etc
contactID (link)

My RSVP table is
RVSPId
RSVP Rcd - yes/no
Accept - yes/no
Decline - yes/no
contactID

I have then combined the tables into a query called FunctionContactQry

SELECT ClientTble.CompanyName, ContactTble.FirstName, ContactTble.LastName, RSVPTble.RSVPRecd, RSVPTble.Accept, RSVPTble.Decline
FROM ClientTble INNER JOIN (ContactTble INNER JOIN RSVPTble ON ContactTble.ContactID = RSVPTble.ContactId) ON ClientTble.ClientID = ContactTble.ClientID;

This query then forms the subform for my Function Form.

So what i need is:
When I select the Type of function eg Xmas party on my FunctionForm (tis is a combo box), i want the subform to automatically pick out all the contacts who have the "yes" ticked to xmas party in the contact table and populate the subform.

I so hope this all makes sense

thanks

 

[dhookom]

Kattz,
Your issue is you table structure is not normalized. You should not have fields like "Xmas Party" and "Golf Day" in your contact table. You should have a junction table between Contacts and Events. If you add an event, you should never have to add a field or control or anything else.



Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Have a look here:

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kattz]

Thanks guys, I actually know about normalization but i couldn't see a way to do it with this one.
Anyway, have changed it and trying to do the Junction Table. It's been a long time since i have worked with Access

cheers