Please help me get my relationships sorted?!!

R

Redlorry72

Self taught on Access and not doing too bad (or so I thought)!! Have built
an event management database with 3 key tables: Beneficiaries, Events and
Organsiations. The beneficiary table includes columns under the headings
Event 1, Event 2 etc whereby I have related this to the Events database under
the Event ID Primary Key. I have forms which show me the event and all the
attendees detailed underneath however this only works with Event 1 column if
a participant attends more than one event have tried to go through the same
process of linking Event 2 column in the Beneficiaries Table to Event ID
Column in the Events table however nothing.....

Does this make sense can anyone help me?
 
D

Douglas J Steele

Sorry to have to say this, but it doesn't sound as though your Beneficiaries
table is properly normalized.

Having fields with names like Event1, Event2, etc. is usually a sign that
you've got a repeating group (which, to be pedantic, is a violation of First
Normal Form, or 1NF) What are you going to do when you find that the table
doesn't contain enough Event fields to handle a particular situation?

Rather, you should have another table (something BeneficiaryEvents) which
has one row for each Event, rather than one field. Unless there are
additional fields that you want to link to a specific Beneficiary Event,
this table would probably only consist of the BeneficiaryID and the EventID.
If the sequence of events is important, you might also add an
EventSequenceNumber (so that EventSequenceNumber 1 would correspond to
Event1 in your existing table, EventSequenceNumber 2 would correspond to
Event2 and so on)
 
R

Redlorry72

Thanks Douglas this does make sense however just made up a table as suggested
BeneficiaryEvents with Reg ID as Autonumber and Beneficiary ID looking up
Beneficiaries table whereby I inserted all fields for that beneficiary and
also to Event ID again looking up events in the Events Table and again
inserted all fields for the event.

I want however to be able to see all the information in a form but am lost
when it comes to relationships.

So far I have:

Beneficiaries Table
Organisation Table
Event Table
Event Type
Contacts Table
Employees Table
Funding Table

Each table is linked by way of "look up" columns to each other. In other
words in the beneficiaries table I look up organisation id, name and address
from organisation table, events id, event type, venue etc. What I would like
is to make up forms to update the beneficiary table, organisation table and
contacts tables as I have one registration form with all this information on
and it would be great if I could update all the information in the same place
rather than updating 3 different tables.

Secondly, I would really like to have a relationship between events and
beneficiaries so I can see by way of form with subform firstly the event with
all the attendees below and secondly the beneficiaries with all the events
they have attended below.

I know this can be done and having been trying to teach myself the
relationship concepts but to no avail - now feel like a complete cluts!!
Please guide me in the right direction.
 
Top