Linking back into the same table 1

[spruceni]

Hi
I have scratched my head on this one for a while.
I have a table of names and addresses.

I want to build a linked system so that I can display on a single form a client's name and address and their associated socal worker's name and address. What mechanism do you use to display two records on the one form?

 

[lespaul]

it's always helpful if you give us the table/fieldnames/key fields. How do you know a person's social worker? I don't see anything in Names or Addresses that links a person to a social worker?



Leslie

Have you met Hardy Heron?

 

[spruceni]

Hi

If it could be understood that I have a table that contains names and addresses some of whom are clients and some are social workers. I also intend to add health trust addresses. The primary key is a field "ContactID" which is autonumbered.

I want to pull up the client's details. If a link to the social worker has already been made then the social worker's address should fill the relevant parts of the form if not, I can then add social worker's address details which will then be stored in the names and addresses table.

I thought about creating an additional table with the clients ContactID and the Socialworker's ContactID as the primary keys. Thereby linking the two together. But I am stuck on how to display the two associated records from the names and addresses table on one form.

 

[lespaul]

I thought about creating an additional table with the clients ContactID and the Socialworker's ContactID as the primary keys. Thereby linking the two together.

that is the correct thing to do:

[tt]
Names
ID FName LName Type(Client/Social Worker??)

Addresses
ID NameID Street City State Zipcode

ClientAssigned
SocialWorkerID ClientID EffectiveDate Active
[/tt]

SELECT C.ID as ClientID, C.FName as ClientFirst, C.LName as ClientLast, S.ID as SWID, S.FName as SWFirst, S.LName as SWLast
FROM ClientAssigned A
INNER JOIN Names C ON A.ClientID = C.ID
INNER JOIN Names SW ON A.SocialWorkerID = SW.ID

you may find these readings helpful:
Fundamentals of Relational Database Design
Understanding SQL Joins


Leslie

Have you met Hardy Heron?

 

[spruceni]

Thanks for all that info. I will have to doodle around it for a while

 

[lespaul]

good luck!

Leslie

 

[MajP]

In certain cases you could do this in one table just like you have it, if (and only if) each person only has one address, and most people are not sharing addreses.

If this is a family practice than you would have a lot of people with the same address so go with what Leslie suggests.

tblPersons
ID
FName
LName
Street
City
State
Zipcode
personType (SW or Patient)
assignedsocialWorkerID (self referencing left null if the record is a social worker)

In other words if record with ID of 1 is a social worker and records 8,9,10 have ID 1 as their social worker then they would have "assignedSocialWorkerID" value of 1.

Now build a form based on a query that returns all records where personType = SW.
Use a subform linked by ID to assignedSocialWOrkerID

 

[spruceni]

Hi lespaul

I played arround with the SQL and came up with

SELECT C.ContactID as CID, C.FirstName as ClientFirst, C.Surname as ClientLast, S.ContactID as SWID, S.FirstName as SWFirst, S.Surname as SWLast
FROM (tblClientSocalWorkerLink A
INNER JOIN tblContactBasic C ON A.ClientContactID = C.ContactID)
INNER JOIN tblContactBasic S ON A.SocWorkerID = S.ContactID;



I added a set of brackets, changed the second join alias from SW to S and a ; at the end. I have made up a sub form based on the query and loaded it into a page on a tab control. Works well. So I have a tab control form based on the client details with lots of pages and one page shows their Social Worker.

Now all I have to do is work out if the client does not have a SW allocated to them how do I let the user select the correct one from the name and address table?