How to pull records from multiple tables

A

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
 
A

azu_daioh

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

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é
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top