Problem joining queries

G

Guest

I’m having a problem joining three tables into one query. I can join the
Investment Table and the Fund Table but when I try to add the Institution
Table and run the query it comes back blank. Here are the details:

Table Funds:
Fund Number (primary key)
Fund Name

Table Institutions:
Institution Name (primary key)
Days to Use

Table Investments:
Investment Number (primary key)
Safekeeping Number
Institution Name
Fund Number
Face Amount
Interest Rate
Other Interest
Purchase Date
Maturity Date

The SQL for the two that join together OK is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM funds INNER JOIN Investments ON funds.[Fund Number] = Investments.[Fund
Number];

The SQL when I add the Institution table is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM Institutions INNER JOIN (funds INNER JOIN Investments ON funds.[Fund
Number] = Investments.[Fund Number]) ON Institutions.[Institution Name] =
Investments.[Institution Name];

I am pretty much self taught and haven’t focused to much on the SQL yet so
I’m hoping that somebody can look at this and explain to me what I’m doing
wrong.

Thanks,
Sue
 
J

Jeff Boyce

Sue

One approach might be to use LEFT (or RIGHT) joins. As I'm reading it, your
SQL looks for values when there is a match in all three tables.

You know your data better than we can ... do you have exact matches on join
keys across all three tables?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
G

Guest

Each Investment will be joined to a fund number and an institution. They all
three work together. I can't have an investment without a fund number or
institution.

Jeff Boyce said:
Sue

One approach might be to use LEFT (or RIGHT) joins. As I'm reading it, your
SQL looks for values when there is a match in all three tables.

You know your data better than we can ... do you have exact matches on join
keys across all three tables?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

skr said:
I’m having a problem joining three tables into one query. I can join the
Investment Table and the Fund Table but when I try to add the Institution
Table and run the query it comes back blank. Here are the details:

Table Funds:
Fund Number (primary key)
Fund Name

Table Institutions:
Institution Name (primary key)
Days to Use

Table Investments:
Investment Number (primary key)
Safekeeping Number
Institution Name
Fund Number
Face Amount
Interest Rate
Other Interest
Purchase Date
Maturity Date

The SQL for the two that join together OK is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM funds INNER JOIN Investments ON funds.[Fund Number] = Investments.[Fund
Number];

The SQL when I add the Institution table is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM Institutions INNER JOIN (funds INNER JOIN Investments ON funds.[Fund
Number] = Investments.[Fund Number]) ON Institutions.[Institution Name] =
Investments.[Institution Name];

I am pretty much self taught and haven’t focused to much on the SQL yet so
I’m hoping that somebody can look at this and explain to me what I’m doing
wrong.

Thanks,
Sue
Click to expand...
Click to expand...
 
J

Jeff Boyce

Then I'm not sure why you are getting an empty result from your query.

If this were mine, I would first join only two tables and check on the
result. Then try the other two-table combination. Finally, I'd try the
'three-at-once' to see if anything changed.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


skr said:
Each Investment will be joined to a fund number and an institution. They all
three work together. I can't have an investment without a fund number or
institution.

Jeff Boyce said:
Sue

One approach might be to use LEFT (or RIGHT) joins. As I'm reading it, your
SQL looks for values when there is a match in all three tables.

You know your data better than we can ... do you have exact matches on join
keys across all three tables?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

skr said:
I’m having a problem joining three tables into one query. I can join the
Investment Table and the Fund Table but when I try to add the Institution
Table and run the query it comes back blank. Here are the details:

Table Funds:
Fund Number (primary key)
Fund Name

Table Institutions:
Institution Name (primary key)
Days to Use

Table Investments:
Investment Number (primary key)
Safekeeping Number
Institution Name
Fund Number
Face Amount
Interest Rate
Other Interest
Purchase Date
Maturity Date

The SQL for the two that join together OK is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM funds INNER JOIN Investments ON funds.[Fund Number] = Investments.[Fund
Number];

The SQL when I add the Institution table is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM Institutions INNER JOIN (funds INNER JOIN Investments ON funds.[Fund
Number] = Investments.[Fund Number]) ON Institutions.[Institution Name] =
Investments.[Institution Name];

I am pretty much self taught and haven’t focused to much on the SQL yet so
I’m hoping that somebody can look at this and explain to me what I’m doing
wrong.

Thanks,
Sue
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, that's what I did. I can join the Investment table and the Fund Table
but I can't join the Institution table to either the Investment table nor the
Fund table and get anything in return.

Jeff Boyce said:
Then I'm not sure why you are getting an empty result from your query.

If this were mine, I would first join only two tables and check on the
result. Then try the other two-table combination. Finally, I'd try the
'three-at-once' to see if anything changed.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


skr said:
Each Investment will be joined to a fund number and an institution. They all
three work together. I can't have an investment without a fund number or
institution.

Jeff Boyce said:
Sue

One approach might be to use LEFT (or RIGHT) joins. As I'm reading it, your
SQL looks for values when there is a match in all three tables.

You know your data better than we can ... do you have exact matches on join
keys across all three tables?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I’m having a problem joining three tables into one query. I can join the
Investment Table and the Fund Table but when I try to add the Institution
Table and run the query it comes back blank. Here are the details:

Table Funds:
Fund Number (primary key)
Fund Name

Table Institutions:
Institution Name (primary key)
Days to Use

Table Investments:
Investment Number (primary key)
Safekeeping Number
Institution Name
Fund Number
Face Amount
Interest Rate
Other Interest
Purchase Date
Maturity Date

The SQL for the two that join together OK is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM funds INNER JOIN Investments ON funds.[Fund Number] =
Investments.[Fund
Number];

The SQL when I add the Institution table is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number], Investments.[Institution Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM Institutions INNER JOIN (funds INNER JOIN Investments ON funds.[Fund
Number] = Investments.[Fund Number]) ON Institutions.[Institution Name] =
Investments.[Institution Name];

I am pretty much self taught and haven’t focused to much on the SQL yet so
I’m hoping that somebody can look at this and explain to me what I’m doing
wrong.

Thanks,
Sue
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

When this happens to me, it's generally because the data doesn't match up.

Have you "manually" confirmed that there are actually matching records
across all three?

Have you "manually" confirmed that the values in your second and third
tables actually match?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

skr said:
Yes, that's what I did. I can join the Investment table and the Fund Table
but I can't join the Institution table to either the Investment table nor the
Fund table and get anything in return.

Jeff Boyce said:
Then I'm not sure why you are getting an empty result from your query.

If this were mine, I would first join only two tables and check on the
result. Then try the other two-table combination. Finally, I'd try the
'three-at-once' to see if anything changed.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


skr said:
Each Investment will be joined to a fund number and an institution.
Click to expand...
They
all
three work together. I can't have an investment without a fund number or
institution.

:

Sue

One approach might be to use LEFT (or RIGHT) joins. As I'm reading
Click to expand...
it,
your
SQL looks for values when there is a match in all three tables.

You know your data better than we can ... do you have exact matches
Click to expand...
on
join
keys across all three tables?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I’m having a problem joining three tables into one query. I can
Click to expand...
join
the
Investment Table and the Fund Table but when I try to add the Institution
Table and run the query it comes back blank. Here are the details:

Table Funds:
Fund Number (primary key)
Fund Name

Table Institutions:
Institution Name (primary key)
Days to Use

Table Investments:
Investment Number (primary key)
Safekeeping Number
Institution Name
Fund Number
Face Amount
Interest Rate
Other Interest
Purchase Date
Maturity Date

The SQL for the two that join together OK is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number],
Click to expand...
Investments.[Institution
Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM funds INNER JOIN Investments ON funds.[Fund Number] =
Investments.[Fund
Number];

The SQL when I add the Institution table is:

SELECT funds.[Fund Number], funds.[Fund Name], Investments.[Investment
Number], Investments.[Safekeeping Number],
Click to expand...
Investments.[Institution
Name],
Investments.[Face Amount], Investments.[Interest Rate], Investments.[Other
Interest], Investments.[Purchase Date], Investments.[Maturity Date]
FROM Institutions INNER JOIN (funds INNER JOIN Investments ON funds.[Fund
Number] = Investments.[Fund Number]) ON Institutions.[Institution Name] =
Investments.[Institution Name];

I am pretty much self taught and haven’t focused to much on the
Click to expand...
SQL
yet so
I’m hoping that somebody can look at this and explain to me what I
Click to expand...
’m
doing
wrong.

Thanks,
Sue
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

total Query 1
Report from two different queries? 3
SQL help - queries very slow 1
Investment database, how to update prices? 0
Database design 7
database design 1
Sum error on select query 1
Certificate of deposit tracking template 0

Top