Client/Entertainment Database

[kattz]

Hi everyone

Just to let you know I only have Access 97 to play with 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
PostalAddress
suburb
Postcode
Phone
Fax
Email
Web


CONTACT TABLE FIELDS
ContactID
ClientID (link)
FirstName
LastName
Position
Phone
Fax
Email

We have a couple of functions through out the year which the contacts may or may not be invited to: Xmas Party and Golf Day.
Some of the contacts will be invited to both and others just to one.

We need to keep a track of RSVPs when they come in
i.e. if they have replied and whether or not they are coming
AND
if they haven't replied at all so this can be followed up.

If i create an Entertainment table with a list of all the functions. How and where do I link/add this table to the other information?

ENTERTAINMENT TABLE
EntertainmentID
TypeOfEntertainment

I thought about having an RSVP table
RSVPId
Yes
No
ContactID (link)

If there is not tick in either the yes or no, then it would be assumed that we have not had a reply. But again, how do i link with the contact info?

It is probably quite simple but i can't get my head around it.


thanks in advance for your help

 

[PWise]

I thought about having an RSVP table
RSVPId
Yes
No
ContactID (link)

I would change this to an ENTERTAINMENTinvite Table

ENTERTAINMENTinviteId
EntertainmentID
ContactID
RSVPed (Yes/no)

 

[MasterRacker]

You can still download the '97 templates.

http://office.microsoft.com/en-us/downloads/CD001022641.aspx

The contact management sample may give you most of what you need with the addition of a table or two.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[kattz]

Unfortunately i can't download the 97 template, for some reason my computer won't let me, so i have to do this on my own. I can't even find anything thru Google either

I have completed the company and the contact details tables with a head form and subform and that works beautifully even added a search.

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, i want the subform to automatically pick out all the contacts who have the "yes" ticked in the contact table and populate the subform.

I so hope this all makes sense

thanks