How to pull records from multiple tables

[azu_daioh]

I have the following tables

Table 1
SSN (PK)
Name
DOB
Address

Table 2
T2recNum (PK)
Entry Date
SSN (FK)
---and other fields for table 2

Table 3
T3recNum (PK)
Entry Date
SSN (FK)
----and other fields for table 3

Table 4
T4recNum (PK)
Entry Date
SSN (FK)
----and other fields for table 4


What I want to do is to create some kind of search function that will
display all the records from Table 2, 3, and 4 with the same SSN.

I want the result to include the following:
SSN --- Table Name --- Entry Date

How do I begin creating this?

Basically, each table consists of multiple fields specific for that
table. The only thing they have in common is the SSN, Name, Address,
DOB.

I already established the relationships but I cant figure out how to
get a list of tables where the SSN is found.


Thank you,

Sharon

 

[azu_daioh]

BTW, The "Name" field as indicated above is just a sample. The actual
fields are "clmtFname," and "ClmtLName"

 

[André Minhorst]

Hi Sharon,

I have the following tables

Table 1
SSN (PK)
Name
DOB
Address

Table 2
T2recNum (PK)
Entry Date
SSN (FK)
---and other fields for table 2

Table 3
T3recNum (PK)
Entry Date
SSN (FK)
----and other fields for table 3

Table 4
T4recNum (PK)
Entry Date
SSN (FK)
----and other fields for table 4


What I want to do is to create some kind of search function that will
display all the records from Table 2, 3, and 4 with the same SSN.

I want the result to include the following:
SSN --- Table Name --- Entry Date

How do I begin creating this?

Basically, each table consists of multiple fields specific for that
table. The only thing they have in common is the SSN, Name, Address,
DOB.

I already established the relationships but I cant figure out how to
get a list of tables where the SSN is found.

just have a look at the SQL UNION keyword.

Bye
André

 

[azu_daioh]

Hi André,

I got the SQL Union ALL to work but I still would like to include the
table name in the result. Any idea how?

Thanks,
Sharon

 

[azu_daioh]

I found out how. Thread closed.

Thank you.