Structural Question (Relationships)

[OxDavis]

Ok I have a series of tables structured like this: --->=1 to many

Service Managers----->Clients-------> Service Received-----> Service Log

However, each client can also have a Case Manager. Usually I'd just make another table for them and create another 1-to-many for that field, but heres the rub: The SERVICE LOG table will have a PROVIDED BY field that will draw from a list of Service Managers and Case Managers, as either one of the two can provide a service. Therefore, I need the 2 types of personnel in one table right? I tried combining the list and adding a TITLE field to discriminate SERVICE MANAGERS from CASE MANAGERS, but am unable to come up with a method that will only display the correctly titled person in the applicable CLIENTS table field.

Thanks in Advance,
Ox

 

[FoFa]

Sounds like your structure should more along the lines of:
Service Log -> Service received
Service manager -> Service received
Case Manager -> Service Recived
Service Received -> Client

What I am missing (and most likely I just don't understand) is something like a work order. Seems to me you could have one work order, many services on that work order, each service could have a service manager or case manager (can it have both?) and a service log tied to a service. Something like this.

 

[OxDavis]

I see what you are saying. The Service Recieved is not just a list of services though, they are date/time specific.

Here are some more specifics about the DB structure. I will post a pic of my relationship tree later this afternoon.

Clients can have a Case Manager and/or Service Manager assigned to them, and will always have at least 1 of the 2.

Most, but not all Clients will have a Service Received.

All Service's Received will have a Service Log.

The Service Log will contain a field for who provided the service.

This "who" can be the Service Manager or the Case Manager, but never both on the same record/entry. (i.e. @ 3:00 the Case Manager can enter a service into the log and @ 4:00 the Service Manager can enter a service into the log)

What I am looking for is a way to make the Case Managers and Service Managers have a 1-to-many to their clients, but at the same time compile a list of options for a pulldown box in the service log to select who provided that service.

Confused yet? I know I am

 

[FoFa]

Almost have ya, just missing something in between the client and services. Does a client always have the same service and or case manager assigned? In other words, would always contact the same manager (bar manager turn over, lets just say they are a service manager for life). So Joe Blow service manager is always clients xxxx service manager? And what do you do for a client, or what is the relationship with a client? A contract, a work order, a case file, what?