Merging tables

[Guest]

I hope this is the same community at the one accessible via Google Groups. I
just noticed that the Google Groups one has not updated since yesterday.

I have two tables. Listings and Loans. Some Listings become Loans, but when
I get the data I am not supplied with a key to link the listings and loans.

I am trying to match some of the other data in the two tables (CreditGrade,
Amount, InterestRate, etc.) to try and reconstruct the relationship between
Listings and Loans.

I'm having difficulty with this.

First, should I create a separate, new table with a column for ListingKey
and a column for LoanKey and then create a relationship between the Loans
and Listings tables via this separate table? Or should I create a new Loan
table that has a column for ListingKey and then populate the listing key in
there as a foreign key to the Listing table?

Secondly, I know I should do an outer join to have all the loans represented
in the resulting table, but I'm also having difficulty constructing the right
query.

How do I explicitly construct the query? My current quesry looks as follows
, but the result is much larger than the number of loans in the loans table.

SELECT Loan.CreationDate, Loan.CreditGrade, Loan.DebtToIncomeRatio,
Loan.GroupKey, Loan.Key AS LoanKey, Listing.Key AS ListingKey
FROM Loan, Listing
WHERE (([loan].[groupkey]=[listing].[groupkey]
And [loan].[CreditGrade]=[Listing].[CreditGrade]
And [Loan].[AmountBorrowed]=[Listing].[AmountFunded]
And [Loan].[CreationDate] > [Listing].[EndDate]));

 

[Guest]

Any help? Am I asking the wrong question?

Where can I fond a good book with the SQL syntax used in Access? It doesn't
seem to accept the SQL syntax of SQL Server 2005.

Thanks.

 

[pietlinden]

Any help? Am I asking the wrong question?

Where can I fond a good book with the SQL syntax used in Access? It doesn't
seem to accept the SQL syntax of SQL Server 2005.

Thanks.

I hope this is the same community at the one accessible via Google Groups. I
just noticed that the Google Groups one has not updated since yesterday.

I have two tables. Listings and Loans. Some Listings become Loans, but when
I get the data I am not supplied with a key to link the listings and loans.

I am trying to match some of the other data in the two tables (CreditGrade,
Amount, InterestRate, etc.) to try and reconstruct the relationship between
Listings and Loans.

I'm having difficulty with this.

First, should I create a separate, new table with a column for ListingKey
and a column for LoanKey and then create a relationship between the Loans
and Listings tables via this separate table? Or should I create a new Loan
table that has a column for ListingKey and then populate the listing key in
there as a foreign key to the Listing table?

Secondly, I know I should do an outer join to have all the loans represented
in the resulting table, but I'm also having difficulty constructing the right
query.

How do I explicitly construct the query? My current quesry looks as follows
, but the result is much larger than the number of loans in the loans table.

SELECT Loan.CreationDate, Loan.CreditGrade, Loan.DebtToIncomeRatio,
Loan.GroupKey, Loan.Key AS LoanKey, Listing.Key AS ListingKey
FROM Loan, Listing
WHERE (([loan].[groupkey]=[listing].[groupkey]
And [loan].[CreditGrade]=[Listing].[CreditGrade]
And [Loan].[AmountBorrowed]=[Listing].[AmountFunded]
And [Loan].[CreationDate] > [Listing].[EndDate]));

The way Access does joins is kind of odd. Instead of creating the
joins in the WHERE clause, it does so in the FROM clause, which I find
confusing. (Especially having written joins in Oracle before doing so
in Access).

the easiest way to do this is to create a query, add your Loan and
Listing tables, and then join them on the matching fields. You could
just add the final criterion as a theta join. (using an inequality).