Problem joining queries

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

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
http://mvp.support.microsoft.com/

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

"skr" wrote in message
news
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

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" wrote:

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
http://mvp.support.microsoft.com/

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

"skr" wrote in message
news
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

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
http://mvp.support.microsoft.com/

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


"skr" wrote in message
...
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" wrote:

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
http://mvp.support.microsoft.com/

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

"skr" wrote in message
news
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

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" wrote:

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
http://mvp.support.microsoft.com/

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


"skr" wrote in message
...
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" wrote:

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
http://mvp.support.microsoft.com/

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

"skr" wrote in message
news 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

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
http://mvp.support.microsoft.com/

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

"skr" wrote in message
...
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" wrote:

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
http://mvp.support.microsoft.com/

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


"skr" wrote in message
...
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" wrote:

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
http://mvp.support.microsoft.com/

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

"skr" wrote in message
news 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