I need help populating a field based upon another table

[Guest]

Field: ClientID Autonumber "9999" Primary Key
This field is a Foreign Key in 3 other tables and I use these seperatley in
(3) Queries to link "Some" Client Details with the relevent records from
other tables and base a forms on the queries.

Each form has a combo box from which the ClientID can be chosen and then
subsequent records are recorded relative to other table. Simple enough!

Given that each of the 3 tables contains information that must be recorded
against each client, ideally I would like "all" of the ClientID's to in the
foreign key. Meaning when I enter a new clientID into the client details
table, the other 3 tables are updated.

I tried using an update query on one table, all the ID's appeared in the
query, but... when I choose run they will not update the field. Doble
checked, Relationships, Data Type etc but itts not happening.

Any Ideas?

 

[TC]

If you design your forms and subforms (if any) properly, Access will
normally populate all the key fields for you. You should not have to
write any code.

What is the structure of each table? (Give us the name of the table,
and the names of the main fields, and show which field(s) are the
primary key for that table.)

Also show us the SQL of the query.

HTH,
TC [MVP Access]

 

[Guest]

Table Structure...With Main Fields Only

tblClientDetails
ClientID...Type=Autonumber Format="9999" Primary Key
FamilyName...Type=Text
GivenName...Type=Text
+some DOB & Address Fields

tblCarerDetails
CareID... Type= Number Format= "9999-CR" Primary Key
ClientID...Type= Number, Format="9999" Foerign Key (On form Selected from
List)
CarerFamilyName...Type= Text
CarerGivenName...Type=Text
Relationship...Type=Number Format="9" (Selected from a code list, combo on
form)
other personal & adsress Fields

tblPrimeDisabilty
DisabilityID... Type=Number Format="99-D" Primary Key
ClientID...Type= Number Format="9999" Foreign Key (On Form Selected from list)
PrimeDisability...Type=Number Format="9" (Selected from a code list, via
combo on form)
Some other related records

tblMdsSubmissionCircumstances
SubmissionID...Type=Number Format="9999-SC" Primary Key
ClientID...Type= Number Format="9999" Foreign Key (On Form Selected from list)
ServiceEpisodeStart Type= Date/Time Format=dd/mm/yyyy
Series of Code fields that are responses to questions, such as, Country fo
Birth EG: 1101= Australia, Main Language Spoken at Home EG:44=English, Living
Arrangements EG: 1=Lives with Family, 2=Lives Alone, 3=Centre Based Care,
9=Unknown/iInadquatley described(and so on for 22 Fields)

tblMdsSubmissionServices
SevicesID...Type=Number Format="9999-SS" Primary Key
ClientID...Type= Number Format="9999" Foreign Key (On Form Selected from list)
Followd by a series of fields for collecting quantative records such as
Total Hours, Total # of Trips, Total Instances & Kind for things such as
Social Support, Transport, Equipment etc

.....................................................MDS=Minimum Data Set...
a quarterly report exported to text file and emailed to Government database
repository

There is another table dealing with Next of kin Details much the same as
Carer Details but NOK is not reportable (in house)

 

[John Vinson]

Given that each of the 3 tables contains information that must be recorded
against each client, ideally I would like "all" of the ClientID's to in the
foreign key. Meaning when I enter a new clientID into the client details
table, the other 3 tables are updated.

It's usually neither necessary nor desirable to create empty
"placeholder" records in the related tables. Typically you would have
a main form for the client table, with three Subforms - one for each
related table; using the ClientID as the master/child link field will
automatically fill in the client id into the child table, *when there
is data to be entered into it*, not before.

A Report can be generated using Outer Join queries - it's not
necessary to have data in all the tables in order to generate a
report, if that's your concern.

John W. Vinson[MVP]

 

[TC]

Ok, I do have some concerns with your table structure. But I do not
want to throw you off the track, by suggesting that you change it all,
if that is not necessary to solve your current problem. So before you
continue, read John Vinson's reply, and see if that will help you solve
your problem. If not, consider my further comments below.

tblClientDetails
ClientID...Autonumber Primary Key
FamilyName
GivenName
+some DOB & Address Fields

That looks fine - with one proviso noted below.


tblCarerDetails
CareID... Primary Key
ClientID
CarerFamilyName
CarerGivenName
Relationship
other personal & adsress Fields

Can one carer, care for more than one client? (Simultaneously, or over
a time period?) If so, then, this table would require you to repeat
that carer's details, in several different records. That is not the way
to go.

If you think about it, carers & clients are both "people", and you
probably want to store the same or similar personal details about each
person, regardless of whether they are carers or clients: family name,
given name, date of birth, current address, and so on. If so, you
should really rename tblClient to tblPerson, and ClientID to PersonID,
and have clients /and/ carers /both/ in that table:

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;
o Relationship is the relationship of that carer, to that client.

Absolutely no personal details (like name or address) should be in that
table. All of the carer's and client's personal details can be obtained
by joining to tblPerson by CarerID or ClientID respectively.

You could evben argue that Relationship should not be in that table,
because the relationship of two given people is constant, regardless of
their "care" relationhips. So you should probably remove Relationship
from that table, and have it in a third (new) table:

tblRelationships
PersonID_1 } composite
PersonID_2 } primary key
Relationship

If you did it as I have suggested above, you would be accurately
reflecting the actual facts of the matter. For example, the fact that
all carers, and all clients, are people. And people can have complex
care relationships. A carer can care for several people. Several carers
can care for the same person. A carer might become, in due course, a
client. Or possibly even vice versa!

The structure that I've suggested above, would deal with all of those
cases, without raising a sweat. Your curent structure would not, IMHO.

HTH,
TC [MVP Access]

 

[Guest]

Thanks John that is helpful along with the table advice from TC I should be
OK! now

 

[Guest]

Thankyou very much, I take on board you concerns and appreiate your time to
provide the examples and advice. Both ourself and John have provided answers
that are very helpful

Ok, I do have some concerns with your table structure. But I do not
want to throw you off the track, by suggesting that you change it all,
if that is not necessary to solve your current problem. So before you
continue, read John Vinson's reply, and see if that will help you solve
your problem. If not, consider my further comments below.

tblClientDetails
ClientID...Autonumber Primary Key
FamilyName
GivenName
+some DOB & Address Fields

That looks fine - with one proviso noted below.


tblCarerDetails
CareID... Primary Key
ClientID
CarerFamilyName
CarerGivenName
Relationship
other personal & adsress Fields

Can one carer, care for more than one client? (Simultaneously, or over
a time period?) If so, then, this table would require you to repeat
that carer's details, in several different records. That is not the way
to go.

If you think about it, carers & clients are both "people", and you
probably want to store the same or similar personal details about each
person, regardless of whether they are carers or clients: family name,
given name, date of birth, current address, and so on. If so, you
should really rename tblClient to tblPerson, and ClientID to PersonID,
and have clients /and/ carers /both/ in that table:

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;
o Relationship is the relationship of that carer, to that client.

Absolutely no personal details (like name or address) should be in that
table. All of the carer's and client's personal details can be obtained
by joining to tblPerson by CarerID or ClientID respectively.

You could evben argue that Relationship should not be in that table,
because the relationship of two given people is constant, regardless of
their "care" relationhips. So you should probably remove Relationship
from that table, and have it in a third (new) table:

tblRelationships
PersonID_1 } composite
PersonID_2 } primary key
Relationship

If you did it as I have suggested above, you would be accurately
reflecting the actual facts of the matter. For example, the fact that
all carers, and all clients, are people. And people can have complex
care relationships. A carer can care for several people. Several carers
can care for the same person. A carer might become, in due course, a
client. Or possibly even vice versa!

The structure that I've suggested above, would deal with all of those
cases, without raising a sweat. Your curent structure would not, IMHO.

HTH,
TC [MVP Access]

 

[TC]

No problems. The best people to help, are those who reply! So we both
appreciate your feedback. Feel free to post again if neccessary.

TC [MVP Access]

 

[Guest]

I do have another question on this matter regarding the relationship/linking
between personID, ClientID & CarerID based upon your suggested table structure

Do I form a relationship between both ClientID&CarerID with PersonID and
would I choose "all records from personID (teble) and only those where the ID
Table match?

 

[TC]

These tables:

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

tblPersonCarers
CarerID } composite
ClientID } primary key

would require these relationships:
tblPersonCarers.CarerID -to- tblPersonDetails.PersonID
tblPersonCarers.ClientID -to- tblPersonDetails.PersonID

with "Enforce referential integrity" set for each one.

HTH,
TC [MVP Access]

 

[Guest]

To meet the reporting requirements of a quarterly minimum data set the
database must collect information in a prescribed format EG: Accommodation
Setting: 1 Private Residence, 2 Shared Accomodation, 3 Cantre Based Care and
so on. the number is the reported code and this is transmitted via an email,
comma deliminated text or CSV file. (I have no problem preparign and sending
these)

I have prepared a number of list tables containing the Field01=Code Field02
= Description. On the form/s I use these as a basis for combo boxes

In most cases only the client is reported on accept when a carer exists and
if respite hours are provided (respite is provided to the carer not the
client)

Fields I must use to collect information:

SLK (Statistical Linkage key) 2nd,3rd,5th letters of FamilName, 2nd,3rd
letters of Given name or 2 if not enough letters (I have this working ok!
Client &Carer(if Carer exists)

DOB...dd/mm/yyyy Client Carer(if carer exists)
DOBEstFlag "9" Client &Carer(if Carer exists)
Country of Birth..."9999" Client &Carer(if Carer exists)
Gender "9" Client &Carer(if Carer exists)
Town Locality "Text" Client &Carer(if Carer exists)
Postal Code "9999" Client &Carer(if Carer exists)
State Identifier "9" Client &Carer(if Carer exists)
Main language Spoken at Home "9999" Client &Carer(if Carer exists)
Indigenous Status "9-99" Client &Carer(if Carer exists)

...........
Accomodation Setting "9" Client &Carer(if Carer exists)
Living Arrangements "9"Client &Carer(if Carer exists)
Govt Pension "9" Client or Carer
DVACard "9" Client or Carer
Carer Existence "9" Client
Carer Residental Status "9-99" Client
Carer Relationship "9-99" Client
Funtional Status "1,2,3,4,5" Client
Additional Funtioanl Status "1,2,3" Client

.........
Entry into service episode dd/mm/yyy Client
Last Assessment dd/mm/yyyy client
Date Ceased with service dd/mm/yyyy client
Reason for Cessation "9-99" Client
.......
Total Hours, Trips or Instances of service
Councelling Advocacy Client
Councelling Advocay Carer
Respite Hours Carer
Social Support Client
Nursing in Home Client
Nursing Centre Client
Allied Care Home Client
Allied Care Centre Client
Day Centre Client
Transport (number of one way trips) Client
Equipment (1,2,3,4,5) Client

EG: of final output
"STARTCLIENT",aneow,"23/12/1959,"Murray
Bridge",5253,4,1,2,1,3,1101,44,1,1,1,2,1,7,"02/10/1948",Tailem
bend",5254,4,1,2,1,2,6,"12/11/2003",16/03/2005,9,9,"ENDCLIENT"

I have restructered my table as you suggested...

To PersonID (I have used ConsumerID)
I have added.. Usual Name Address/Contact Details +
SLK,Gender,COB,DOBEstFlag,MainLanguage,Indigenous Status,StateIdentifier

NOW!...? is this correct?
tblConsumerCircumstances
Primary & Foreign Keys????????
Carer Exitsence
Accomodation Setting
Living Arrangements
Govt Pension
Carer Relationship
Carer Residency Status
Functional Status
Additional Functional Status

tblConsumerServices
ClientID (Foreign Key)?...Relationship?...(PersonID,ClientID?????)
CarerID (Foreign Key)?... Relationship?
All of the fields relating to quantity of services

tbl???? (to handle as follows)
ClientID (Foreign Key)
Service entry
Last Assessment
Cessation Date
Reason for cessation

These tables:

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

tblPersonCarers
CarerID } composite
ClientID } primary key

would require these relationships:
tblPersonCarers.CarerID -to- tblPersonDetails.PersonID
tblPersonCarers.ClientID -to- tblPersonDetails.PersonID

with "Enforce referential integrity" set for each one.

HTH,
TC [MVP Access]

 

[Guest]

Found an issue with this table structure?

it assumes all Clients have carers which is not the case

Person ID Table...requires a unique record for each field (composite primary
key)

ClientID that have no carer cannot be rorded in this table

Would it not be easier to provide a yes/no field in the tblperson
(Carer_Yes/No) then run parameter query (Criteria TRUE) to extract the carer
info?