Matching records in different tables

[MikeB]

I have two tables Listings and Loans. A listing (but not all listings)
becomes a loan, but the provider of the data does not provide me with
a key between the Listings table and the Loans table.

Now I'm trying to construct my own cross-reference table. I have data
in the Listings table and the Loans table that I can use to help me
make an approximate match-up. I have credit grade, loan amount,
Interest rate and date closed/originated. What I'm hoping to end up
with is a crossreference table that has a Loan key and a Listing key.

Here is my SQL:

SELECT Loan.Key, Listing.Key, Loan.BorrowerRate, Loan.GroupKey,
Listing.Title, Loan.OriginationDate, Listing.StartDate,
Listing.EndDate, Loan.DebtToIncomeRatio INTO LoansListings
FROM Listing, Loan
WHERE (((Loan.BorrowerRate)=[listing].[borrowerRate]) AND
((Loan.GroupKey)=[listing].[groupkey]) AND
((Loan.OriginationDate)>[listing].[endDate]) AND
((Loan.DebtToIncomeRatio)=[Listing].[DebtToIncomeRatio]) AND
((Loan.AmountBorrowed)=[listing].[amountrequested]) AND
((Loan.CreditGrade)=[listing].[creditgrade]) AND ((([Listing].
[Status])="Withdrawn" Or ([Listing].[Status])="Expired" Or ([Listing].
[Status])="Active" Or ([Listing].[Status])="Cancelled")=False));


My problem now is that not all the loans are in the cross-reference
table, and I can't figure out why not.

Is there some what I can now run a query to list loans who do not have
a key in the LoansListings table? Or can I get a record in the
LoanListings table with a blank record for the listing key?

Thanks.

 

[pietlinden]

I have two tables Listings and Loans. A listing (but not all listings)
becomes a loan, but the provider of the data does not provide me with
a key between the Listings table and the Loans table.

Now I'm trying to construct my own cross-reference table. I have data
in the Listings table and the Loans table that I can use to help me
make an approximate match-up. I have credit grade, loan amount,
Interest rate and date closed/originated. What I'm hoping to end up
with is a crossreference table that has a Loan key and a Listing key.

Here is my SQL:

SELECT Loan.Key, Listing.Key, Loan.BorrowerRate, Loan.GroupKey,
Listing.Title, Loan.OriginationDate, Listing.StartDate,
Listing.EndDate, Loan.DebtToIncomeRatio INTO LoansListings
FROM Listing, Loan
WHERE (((Loan.BorrowerRate)=[listing].[borrowerRate]) AND
((Loan.GroupKey)=[listing].[groupkey]) AND
((Loan.OriginationDate)>[listing].[endDate]) AND
((Loan.DebtToIncomeRatio)=[Listing].[DebtToIncomeRatio]) AND
((Loan.AmountBorrowed)=[listing].[amountrequested]) AND
((Loan.CreditGrade)=[listing].[creditgrade]) AND ((([Listing].
[Status])="Withdrawn" Or ([Listing].[Status])="Expired" Or ([Listing].
[Status])="Active" Or ([Listing].[Status])="Cancelled")=False));

My problem now is that not all the loans are in the cross-reference
table, and I can't figure out why not.

Is there some what I can now run a query to list loans who do not have
a key in the LoansListings table? Or can I get a record in the
LoanListings table with a blank record for the listing key?

Thanks.

Lossy joins. If you want to keep all the records from one of the two
tables, use an outer join. If you want all the records from both
tables, you're SOL, because Access can't do a full outer join. Maybe
you can fake it out by creating two outers (left and right) and union
the results together.