Client/Event Database help

[akrasodomski]

Hi there,

I'm looking for some help or examples in trying to learn how to link two different 'types' of thing together. I want a database of clients and a number of events. We have lots of different types of clients, so I think they would be held in different tables. Clients and events should be able to link to the other, so it can:

1. Add clients to events.
2. Produce lists of clients that have been invited to an event, and a seperate one for those who have said they would come.

I think that's it - sorry if this isn't clear - I'm not sure what more to explain.



If anyone can suggest a link to some examples or tutorials, or just offer some advice, that would be great.

Thanks,

Alex

[jzwp11]

We have lots of different types of clients, so I think they would be held in different tables

A client is still a client no matter what type, so all clients should be in 1 table. You can add a field (related to a table of types) to identify the type of client. That would look like this:

tblClients
-pkClientID primary key, autonumber
-txtPrimaryName (company name or last name if an individual)
-txtSecondaryName (the first name if an individual)
-fkClientTypeID foreign key to tblClientTypes

tblClientTypes
-pkClientTypeID primary key, autonumber
-txtClientTypeName

Now, since many clients can attend an event, and I assume that a client can attend many events over time, that describes a many-to-many relationship between client and events. To handle a many-to-many relationship we need a junction table

tblEventClient
-pkEventClientID primary key, autonumber
-fkEventID foreign key to tblEvents
-fkClientID foreign key to tblClients

tblEvent
-pkEventID primary key, autonumber
-txtEventName

Now, do each of your events happen multiple times and/or at multiple locations? If so, you will need to tweak the above and will need more tables.

In order to help you further, we would need more details about your application and your business process.

[akrasodomski]

JZWP11,


Thank you so much for the reply - I figured it was something to do with relationships that I was missing.

I think I have followed your instructions (I started again). This is what I have so far, although if FK are supposed to be manually added in I've done it wrong.

http://i41.tinypic.com/9uugcw.jpg

I hope you might be able to help me further: I'll try and flesh it out a bit. As I said, we run events - pamphlet launches and speeches - to which we invite people stored in an ever-growing excel document. Currently the process involves creating a new excel spreadsheet for an event and copy and pasting in the various people we wish to invite (journalists2010.xls, educationstaff.xls etc. Nightmarish).

I figure access ought to be able to - very rapidly - sort this out. It is a slightly guerilla campaign as my director is an ardent traditionalist and doesn't like things not in Word. Still, the struggle continues.

How do you think I might go about replicating this process in Access? Please, please ask if I can give you any more information/help!

Alex

[jzwp11]

In your client table, you should have the clienttypeID field and make the join to the tblClientTypes using that field not the clienttypename field. You would use forms for data entry. Users should never see your tables.