Jul 16, 2009 14:16
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\
I need to do a report showing all loans to a given customer, and all guarantors on those loans. The tools that I have for accomplishing this task are Crystal Reports and Excel 2003.
This information is stored in an SQL database, in a format where Customer is linked to Loans and Loans are linked to Guarantors. So, for example:
Foo Inc has loan #s 100, 101, 102 and 103. Loan #100 is guaranteed by Fi Foo and Fum Foo. Loan #101 is guaranteed by Fi Foo, Fum Foo, and Fi Fum Enterprises. Loan #s 102 & 103 hav no guarantors at all.
Now, I can query my database using Crystal Reports and get the following table:
Borrower
Loan Number
Guarantor
Foo Inc
100
Fi Foo
Foo Inc
100
Fum Foo
Foo Inc
101
Fi Foo
Foo Inc
101
Fum Foo
Foo Inc
101
Fi Fum Enterprises
Foo Inc
102
--
Foo Inc
103
--
But I actually don’t care which loans are guaranteed by which individuals. All I want is each unique loan number and each unique guarantor. So what I want is more like:
Borrower
Loan Number
Guarantor
Foo Inc
100
Fi Foo
Foo Inc
101
Fum Foo
Foo Inc
102
Fi Fum Enterprises
Foo Inc
103
--
Or, perhaps less confusing:
Borrower
Loan Number
Guarantor
Foo Inc
100
Fi Foo
101
Fum Foo
102
Fi Fum Enterprises
103
Aaaaand …
I can’t figure out how to do this. >.<
If I wanted to show all the guarantors and didn’t care if I showed all the loans or not, I could do that by grouping on the guarantors and hiding the details (where all the loans would be hidden). Or vice-versa, if I wanted to show all the loans and didn’t care if I showed all the guarantors or not. But I need to show the unique results for each, and I need to do this for 100+ borrowers, who may have only one loan and no guarantors, or might have 20 loans with eight guarantors each. Showing only unique instances would be the difference between a report that fits on 10 pages and one that takes up 200.
I don’t want to do this by hand, and I don’t want the people who are currently doing it manually keep doing it manually, because that is suckage beyond words. Gah.
work