Report organization

[kstyles]

I'm creating a report that will summarize the outstanding items each person has. There are two areas that I have to add to the report - a borrower and their guarantors. There is one borrower and many guarantors. There are also many outstanding items per borrower.

However, when I create my report the outstanding items are duplicated, one set of outstanding items for each guarantor. EX
name oustanding item
George Jetson...............tax returns
Jane Jetson..................financial statement
Scooby Doo..................collateral evaluation


George Jetson...............financial statement
Jane Jetson..................collateral evaluation
Scooby Doo..................tax returns
etc....

The tables are set up so that each annual review has a number that is the key. The guarantors have their own table with the annual review number as well as the outstanding items table has an annual review number too. I have set the relationship to enforce integrity.

I just want the report to list the outstanding item once and the guarantors once. I have tried hiding duplicates and that doesn't seem to work either. Any suggestions?? I'd greatly appreciate it! THANKS!!

[weekend00]

do you mean that you want this way?
George Jetson...............tax returns, financial statement
Jane Jetson..................financial statement,collateral evaluation
Scooby Doo..................collateral evaluation,tax returns

there are many threads in this forum about this issue, but I could not remember which thread.

[kstyles]

I couldn't find anything specific to what I wanted...

Here's what I would like to see... I was thinking I may have to do a subreport within my report to get the final organization I want.

Anyway this is what I want:

George Jetson...............tax returns
Jane Jetson..................financial statement
Scooby Doo..................collateral evaluation

No repeating. Basically I'm preparing a report for a committe that shows the borrower, their guarantors, and the outstanding items. So I don't want anything duplicated. One borrower, each guarantor, and all of the outstanding items.

[weekend00]

select borrower, first(guarantor) from yourtable group by borrower.

[kstyles]

is this a formula I should put in the control source for the guarantor properties?

[kstyles]

IS there a way to select DISTINCt from within the expression builder for an item? I've been playing around but it seems whenever I close out the box my expression goes away.

[weekend00]

you should use the query as data source of the report.

if you already have borrower in you report and want to get guarantor, you can put formula =dlookup("guarantor","yourtablename","borrower='" & borrower & "'") in the control source for the guarantor properties. (the name of the textbox for borrower must be "borrower")

[kstyles]

I don't think this is working for me! I'm having a hard time because for some annual reviews (that's my key) there can be more outstanding items than guarantors.

So in my query it's organizing the outstanding items one time for each guarantor. This seems okay to me because in table format there has to be something in each field. But for report organization purposes I only want to see the guarantor once and the outstanding item once (but I want to see each unique value).

I already have the borrower, the guarantor, the outstanding items in my query. Should I take them out? I'm gonna keep working on it... seem to always get back to where I started with this one!