How to pull records from multiple tables

  • Thread starter Thread starter azu_daioh
  • Start date Start date
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
 
BTW, The "Name" field as indicated above is just a sample. The actual
fields are "clmtFname," and "ClmtLName"
 
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.
Click to expand...

just have a look at the SQL UNION keyword.

Bye
André
 
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
 
Back
Top