Multiple Relationships in Microsoft Access 1

[sianpembs]

Hi!
I'm constructing a database being made up of four tables, Volunteer details, Worker details, Client Details and Volunteer status. Each Volunteer, worker and client has an autonumber-assigned ID, which is the primary key in each respective table. I want to create several relationships:
1)In table "Volunteer" to link "Volunteer_ID" column (primary key) to "Volunteer_ID" column in "Client" table - one to one.
2)In table "Worker" to link "Worker_ID" to "Worker_ID in table "Clients" - a one to many relationship. This is where I hit problems. Microsoft tell me that either this is too complicated or I've entered the "expression" wrongly. What I want to happen is that in the "Workers" table next to the primary key column (i.e. "Worker_ID) appears a little "plus" sign, which when clicked will display the associated records from table "Clients" underneath.
Also, in addition to 1) I want to link the volunteer ID to their status in table "Volunteer status", although I'm not sure whether I can do this in addition to 1) in the same table.

Plus, can someone please tell me how to change the type of relationship (one-to-one, one-to-many, or many-to-many) when Microsoft Access gets it wrong.

Microsoft access help is about as clear as mud on this topic, and not being an IT pro (far from it) I'm getting very frustrated as I can't see what I'm doing wrong. Any help would be much appreciated!!

Sian

 

[PHV]

All that you want may be done in the RelationShips window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Sian

Let me try to understand the objective of your database.

You have clients and volunteers.
Are workers the same as volunteers? Can workers include volunteers? Or are workers totally different than voluntters?

I assume voluntters can work for clients. For example, the volunteers could be people who visit elderly clients.

Now...
Can a volunteer look after more than one client?
Can a client have more than one volunteer attend to them?
If so, this is a many-to-many relationship. The way Access handles a many-to-many relationship is to use a "joiner" table.

I am guessing of course, but assuming...

tblClient
Client_ID - primary key
ClientLN - last name
ClientFN - first name

tblVolunteer
Volunteer_ID - primary key
VolunteerLN
VolunteerFN
CurrentStatus - text (Active, Retired, AtSchool...)

tblClientVolunteerProfile
Client_ID - foreign key to tblClient
Volunteer_ID - foreign key to tblVolunteer
StartServiceDate - date
EndServiceDate - date
ServiceType - text (FullService, LimitedService, etc)

Primary key = Client_ID + Volunteer_ID

If a volunteer has different "active" periods, then
Primary key = Client_ID + Volunteer_ID + StartServiceDate

I added the Start and End service date to track activities of past volunteers. A profile record with no EndServiceDate would suggest the currently active volunteer.

The service type would tack the type of service provided.

...Moving on to Details
Are your "details" a type of history or notes of the visits / encounters? Your design should work. The only thing I am concerned about is how to track your "details" for a volunteer x client.

There are a couple of ways. Just guessing again...

tblEncounterHistory
HistoryID - primary key
Client_ID - foreign key to tblClient
Volunteer_ID - foreign key to tblVolunteer
EncounterDate - date
Notes - memo field

Still not sure about your "workers". But if a worker and volunteers both "work", you can perhaps treat volunteers as a "type" of worker...

tblWorker
Worker_ID - primary key
LastName
FirstName
CurrentStatus - text (Active, Retired, AtSchool...)
WorkerType - text (Volunteer, Medical, Paid)

And then change the other tables as required. (Volunteer_ID --> Worker_ID)

... where I hit problems ...

I know this is a long post, but your comment about difficulties with the creation of relationships suggests to me that you have a M:M relationship but you are trying to use a 1:M relationship.

Hopefully, this helps.
Richard