maintain an identical table with different name but same data 1

[keun]

Hi. I need to make a table (tblClientRealted) that has all the same data as my tblClient. I wrote an append query to populate is but do not want to have to append everytime a record is added to tblClient. Is there a way to make this happen more easily? I know you folks want to know why... I have a table that tracks how clients are related to eachother (not in a database way, in a personal way). The query pulls ClientID from the tblClient table and stores it, this is fine. But I want to be able to also store the related client data in a way that will display the RelatedClientID as well and the name of the RelatedCLient. Because of the way the relationships are set up the name that shows is the ClientID's name, not the RelatedClientID's name. I solved the problem by pulling both tables together in the query. I am new at this so it was the only way I knew how to do it and it works fine is I can get the cloned table to work. Worst case scenario I just delete tblClientRelated (weekly) and replace it with a copy of tblClient that I then name tblClientRelated. But since I am handing the database off to users I want it to work a little nicer than that!

 

[PHV]

No need to duplicate the table, have a look at self join and alias:
SELECT C.*, R.*
FROM tblClient AS C LEFT JOIN tblClient AS R ON C.RelatedClientID = R.ClientID

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

 

[keun]

Like so many users, I have to ask - Where does this go?

 

[PHV]

This is SQL code you can experiment in the SQL view pane of the query window.

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

 

[keun]

This is what is in there now:

SELECT tblClientSubRelations.ClientID, tblClientSubRelations.ClientRelated, tblClientSubRelations.[Nature of relationship], tblClientsRelations.ClientGiven, tblClientsRelations.ClientFamily
FROM tblClientsRelations INNER JOIN tblClientSubRelations ON tblClientsRelations.ClientID = tblClientSubRelations.ClientRelated;

What do I replace?

 

[PHV]

Provided your posted query worked for you:
SELECT S.ClientID, S.ClientRelated, S.[Nature of relationship], R.ClientGiven, R.ClientFamily
FROM tblClientsRelations AS R INNER JOIN tblClientRelations AS S ON R.ClientID = S.ClientRelated

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

 

[keun]

Looking at your SQL I think that I described the problem poorly because you included tblClientRelations which is actually the "cloned" table which you are helping me get rid of.

The query that I posted works for me. Using this code you generously provided: when I switch from SQL View to Datasheet View I get two message boxes requesting Parameter Value. The first asks for S.ClientRelated and the second asks for S.NatureOfRelationship.

To be clear, because I left out the fact that this data is all stored in a new table: I have the table tblClients, with the field ClientID. The query brings together (for use in a sub-form) ClientID (which relates to the main form that the subform sits on), a second ClientID (which refers to the Client with the relationship) and an additional field, NatureOfRelationship, which the user types in. I want the first and last name of the second ClientID to show on the form (which is based on the query). All of this data is then stored in the tblSubClientRelations. Once it is all said and done, tblSubClientRelations should store a ClientID, a second Client ID for the related Client, and the nature of the relationship. When I look at the form I want to see these three fields, plus the first and last name of the related client.

I appreciate the help and also your patience. I think I am very close and would hate for you to give up on me! I can start from scratch, re-describing the problem if I have made a mess of it.

 

[PHV]

Have you understood my post dated 16 Aug 05 1:59 ?

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

 

[keun]

I guess not, because I thought that you post 16 Aug 05 12:18 trumped the first one!

 

[PHV]

Say you have the following layout:[tt]
tblClients
ClientID (PK)
ClientName

tblClientSubRelations
ClientID (FK to tblClients)
RelatedClientID (FK to tblClients)
NatureOfRelationship[/tt]

SELECT S.ClientID, C.ClientName, S.RelatedClientID, R.ClientName AS RelatedClientName, S.NatureOfRelationship
FROM (tblClientSubRelations AS S
INNER JOIN tblClients AS C ON S.ClientID = C.ClientID)
INNER JOIN tblClients AS R ON S.RelatedClientID = C.ClientID;

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

 

[keun]

I do not blame you if you do not respond at this point. I went back through and made sure that all my fields and table names are identical to yours. I get an error when switching from SQL to Datasheet. The error says, "Join Expression Not Supported" and the "S.RelatedClientID = C.ClientID" and the very end of the query is highlighted.

I double-checked the relationship. I am going one to many from ClientID in tblClients to ClientID in tblClientSubRelations and also one to many from ClientID in tblClients to RelatedClientID in tblClientSubRelations.

I even tried to design a new query in design view and cancel the show table and just paste right into the SQL view. Same error.

 

[PHV]

Sorry for the typo:
S.RelatedClientID = [highlight]R[/highlight].ClientID

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

 

[keun]

THIS WORKED! Thank you so much for your help.

Can you answer one more question for me? What if I want another field (ClientFirstName) in addition to ClientName?

If you answer this I will be so dialed in, not only that, it will help me understand the query better.

Thanks for all the help

 

[PHV]

SELECT S.ClientID, C.ClientName, ClientFirstName, S.RelatedClientID, R.ClientName AS RelatedClientName, R.ClientFirstName AS RelatedClientFirstName, S.NatureOfRelationship
FROM (tblClientSubRelations AS S
INNER JOIN tblClients AS C ON S.ClientID = C.ClientID)
INNER JOIN tblClients AS R ON S.RelatedClientID = R.ClientID;

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

 

[keun]

BEAUTY. Got it. Thanks a bunch.

 

[SeeThru]

Go on....give him a star....