Excluding related records in a query

[Guest]

I have a borrower table, which has a field called RA CIF used to group
related borrowers. The number is entered by the user. There is another
table, loan info, that is linked to the borrower table.

I want to create a query that will include only those loans from related
borrowers (those that have the same RA CIF #). The query will be the basis
of a form linked to the borrower. For example, the following borrowers are
related: Joe Smith, Smith, Inc., and Smith & Sons. When I open the borrower
form for Joe Smith, I want to see the list of loans for Smith, Inc. and Smith
& Sons, but not for Joe Smith.

Below is the SQL statement for the current query:

SELECT Borrower.[ra cif], [Loan Info].balance, ([balance]+[unfunded]) AS
comitted, Borrower.Borrower, [Loan Info].[Loan No]
FROM Borrower INNER JOIN [Loan Info] ON Borrower.Borrowerid = [Loan
Info].borrowerid
ORDER BY ([balance]+[unfunded]) DESC;

Any ideas would be greatly appreciated.

Thank you,

Kelvin

 

[Jeff Boyce]

It sounds like you could have zero, one, or many "related" loans. If that's
the case, then you have a one-to-many relationship. One way of displaying
this in forms is to use a main form for the "one" side information, and a
subform to display the related (many) records.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thanks for your input. There will never be a case where a borrower has zero
loans, by definition. As for the one to many, etc., that I know. I have a
main form for borrower and a subform for loan info stuff. I want to put on
the borrower form a list of related borrowers with their total loans. I have
that form created, except it shows the loans for the current borrower, hence
the desire to exclude those loans and only show the other relationships.

So in my example, the borrower form shows all of Joe Smith's loans and in a
separate list shows the related borrowers and their total loans.

Kelvin

It sounds like you could have zero, one, or many "related" loans. If that's
the case, then you have a one-to-many relationship. One way of displaying
this in forms is to use a main form for the "one" side information, and a
subform to display the related (many) records.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a borrower table, which has a field called RA CIF used to group
related borrowers. The number is entered by the user. There is another
table, loan info, that is linked to the borrower table.

I want to create a query that will include only those loans from related
borrowers (those that have the same RA CIF #). The query will be the
basis
of a form linked to the borrower. For example, the following borrowers
are
related: Joe Smith, Smith, Inc., and Smith & Sons. When I open the
borrower
form for Joe Smith, I want to see the list of loans for Smith, Inc. and
Smith
& Sons, but not for Joe Smith.

Below is the SQL statement for the current query:

SELECT Borrower.[ra cif], [Loan Info].balance, ([balance]+[unfunded]) AS
comitted, Borrower.Borrower, [Loan Info].[Loan No]
FROM Borrower INNER JOIN [Loan Info] ON Borrower.Borrowerid = [Loan
Info].borrowerid
ORDER BY ([balance]+[unfunded]) DESC;

Any ideas would be greatly appreciated.

Thank you,

Kelvin

 

[Jeff Boyce]

Kelvin

Ah, so if you could list the "others", that would be good enough?

Can you add a listbox control to the form and populate it with a query that
returns all the related loans except the selected borrower?

The query could point to the form to get both the select borrower (and
exclude that one), and the "Group ID" of the selected borrower.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your input. There will never be a case where a borrower has
zero
loans, by definition. As for the one to many, etc., that I know. I have
a
main form for borrower and a subform for loan info stuff. I want to put
on
the borrower form a list of related borrowers with their total loans. I
have
that form created, except it shows the loans for the current borrower,
hence
the desire to exclude those loans and only show the other relationships.

So in my example, the borrower form shows all of Joe Smith's loans and in
a
separate list shows the related borrowers and their total loans.

Kelvin

It sounds like you could have zero, one, or many "related" loans. If
that's
the case, then you have a one-to-many relationship. One way of
displaying
this in forms is to use a main form for the "one" side information, and a
subform to display the related (many) records.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a borrower table, which has a field called RA CIF used to group
related borrowers. The number is entered by the user. There is
another
table, loan info, that is linked to the borrower table.

I want to create a query that will include only those loans from
related
borrowers (those that have the same RA CIF #). The query will be the
basis
of a form linked to the borrower. For example, the following borrowers
are
related: Joe Smith, Smith, Inc., and Smith & Sons. When I open the
borrower
form for Joe Smith, I want to see the list of loans for Smith, Inc. and
Smith
& Sons, but not for Joe Smith.

Below is the SQL statement for the current query:

SELECT Borrower.[ra cif], [Loan Info].balance, ([balance]+[unfunded])
AS
comitted, Borrower.Borrower, [Loan Info].[Loan No]
FROM Borrower INNER JOIN [Loan Info] ON Borrower.Borrowerid = [Loan
Info].borrowerid
ORDER BY ([balance]+[unfunded]) DESC;

Any ideas would be greatly appreciated.

Thank you,

Kelvin