I wrote this out in the hopes that I'd figure out a solution once I had it laid before me, but nooooo. So I'm turning to my much smarter readers for advice. /o.o
( Read more... )
On the SQL-fu front, what you really want is two select distincts within each borrower with the results merged together. I could do it cleanly (generating one set of output results) using a SQL procedural language (like PL-SQL for Oracle or T-SQL for SQL Server), but here's something in vanilla SQL which doesn't come that far from what you want:
Select Distinct Borrower, Loan From Borrowers left outer join loans on Borrowers.BorrowerID = Loans.BorrowerID Order by Borrower, Loan
Select Distinct Borrower, Guarantor From Borrowers left outer join loans on Borrowers.BorrowerID = Loans.BorrowerID left outer join Guarantors on Loans.LoanID = Guarantors.LoanID Order by Borrower, Guarantor
I know nothing about crystal reports, but there is probably a way to use it to create simple queries like these (just a two table join with a distinct). Btw, those joins are "left outer" so that you also get a NULL row to indicate when a borrower is in the system, but has no loans or no guarantors.
This gives you the data you want (distinct loans and guarantors for each borrower). It's just in two separate lists that you will then have to merge to build the final report.
Doing them as distinct lists I can manage. It's the merge part that I can't figure out -- how to get the list of borrwers & loans to line up with the list of borrowers & guarantors. :/
One way to do it would be to write an Excel macro to insert blank lines in each list until the borrowers are spaced one borrower per 10 lines (or whatever number it takes to cover the largest number of rows for any borrower). That will leave you with both lists in the same alignment. Then copy the data from one list to the other one. At that point, you have a single list (if with a few extra blank lines) which is just a whitespace eradicating macro away from what you want.
Select Distinct Borrower, Loan
From Borrowers left outer join loans
on Borrowers.BorrowerID = Loans.BorrowerID
Order by Borrower, Loan
Select Distinct Borrower, Guarantor
From Borrowers left outer join loans
on Borrowers.BorrowerID = Loans.BorrowerID
left outer join Guarantors
on Loans.LoanID = Guarantors.LoanID
Order by Borrower, Guarantor
I know nothing about crystal reports, but there is probably a way to use it to create simple queries like these (just a two table join with a distinct). Btw, those joins are "left outer" so that you also get a NULL row to indicate when a borrower is in the system, but has no loans or no guarantors.
This gives you the data you want (distinct loans and guarantors for each borrower). It's just in two separate lists that you will then have to merge to build the final report.
Reply
Reply
Reply
Leave a comment