Advice on table structure needed please

[Guest]

tblconsumer
personID Primary Key Auto Number "9999"
Followed by main records

tblclient_carer_id
clientID "9999" Composite
carerID "9999" Key

Both field related to personID, referential integrity checked yes


Problem 01...
Some Clients do not have a carer
tblclient_carer_id require that records exist in both fields

Problem02
I need some way of transferring/updating the PersonID to the CLient/CarerID
Table

 

[Allen Browne]

If I understand you, all people (clients and carers) are entered into your
tblConsumer table. If a client has a carer, then you also have a record in
the tblclient_carer_id. This table has 2 foreign keys to tblConsumer, so in
the Relationship window you have 2 copies of tblConsumer (Access will alias
the 2nd one as tblConsuemer_1), with a relationship each way.

Problem 1:
What's the problem? If a person has no carer, the have no matching record in
tblclient_carer_id. If you create a query using both tables, double-click
the line joining the 2 tables in the upper pane in query design. Access pops
up a dialog offering 3 choices. Choose the one that says:
All records from tblConsumer, and any matches from...

More info in article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Problem 2:
The solution above means you don't need a record in the related table if the
person has no carer.

If you do want the record anyway, you cannot use a composite primary key if
one of the fields can be null, so you will need to redesign the table. You
could then use the AfterInsert event procedure of the form where you enter
the consumer, to Execute an Append query statement (SQL string) to add the
record to the related table as well. But I don't think this is a useful
thing to do.

 

[John Vinson]

Problem02
I need some way of transferring/updating the PersonID to the CLient/CarerID
Table

As Allen says, Problem 1 is a non-problem: if there's no carer, you
don't need a record in this table at all.

For Problem 2, the usual way is to use a Form based on the Client
table, with a subform based on the ClientCarer table; use the ClientID
as the master/child link field, and it will fill in the ClientID
automatically when you select a carer.

Don't use table datasheets for routine data entry or editing - they're
much too limited. Use Forms instead.

John W. Vinson[MVP]

 

[Guest]

Problem1 is so because ClientID and CarerID are not foreign keys?

They are as advised by TC when answering an earlier question that I had

See the advice below....

tblPersonDetails
PersonID...Autonumber Primary Key
FamilyName
GivenName
DOB, Address etc.

Now, to record the fact that person #111 cared for person #222, all you
need is the following table, /replacing/ tblCarerDetails:

tblPersonCarers
CarerID } composite
ClientID } primary key
Relationship
*NO* personal data (names, addresses etc.)

In that table:
o CarerID is the PersonID of the person providing the care;
o ClientID is the PersonID of the person receiving the care;
o the primary key is BOTH those fields; this is called a "composite"
primay key; it is a single primary key, comprising of TWO FIELDS;

The above seems to be ok if each client has a carer....but some don't

So do I make CarerID primary and add the consumerid(name replacement for
personID) as the foreign key?

 

[John Vinson]

Problem1 is so because ClientID and CarerID are not foreign keys?

I do not understand.

If there is no carer, there is no CarerID, and there is no record in
this table. This has ABSOLUTELY NOTHING WHATSOEVER to do with whether
CarerID is a foreign key.

They are as advised by TC when answering an earlier question that I had

See the advice below....

tblPersonDetails
PersonID...Autonumber Primary Key
FamilyName
GivenName
DOB, Address etc.

Now, to record the fact that person #111 cared for person #222, all you
need is the following table, /replacing/ tblCarerDetails:

tblPersonCarers
CarerID } composite
ClientID } primary key
Relationship
*NO* personal data (names, addresses etc.)

And if NOBODY cared for Person #222, there would simply not be any
record in tblPersonCarers with a ClientID of #222. No carer - no
record.

In that table:
o CarerID is the PersonID of the person providing the care;
o ClientID is the PersonID of the person receiving the care;
o the primary key is BOTH those fields; this is called a "composite"
primay key; it is a single primary key, comprising of TWO FIELDS;

The above seems to be ok if each client has a carer....but some don't

So do I make CarerID primary and add the consumerid(name replacement for
personID) as the foreign key?

By simply NOT DOING IT.

If you don't have a carer, *don't insert a record in this table*.

John W. Vinson[MVP]

 

[Guest]

Then I must set the required property as "NO" currently as ClientID&CarerID
are composite primary key, an Error occurs if you enter a ClientID without a
CarerID

 

[John Vinson]

Then I must set the required property as "NO" currently as ClientID&CarerID
are composite primary key, an Error occurs if you enter a ClientID without a
CarerID

I'm sorry.

YOU ARE NOT READING WHAT WE ARE SAYING.

Please... look again.

If there is no Carer for a Client, you do not need a CarerID field AT
ALL.

You do not need a CarerID value in the ClientCarers table because for
that client, *you do not enter ANYTHING WHATSOEVER* into the
ClientCarers table.

If there is no record in the table, then the question of whether the
CarerID field is required or not is moot - because *there's nothing
there*.

John W. Vinson[MVP]