Query on subform data produces unwanted duplicates

[ezaccess]

I have a database that keeps track of donations. In the one table I have the donors unique information; name, address, etc. In another table I keep track of the donations by date, type and amount. This data is entered on a form that contains a subform that tracks the donations.

Problem: Each time I query the database for the names of the donors who have made a donation let's say for the last 6 months, I get the donors name in the query results for each hit that satisfies the query. For example, if the donor made 10 donations within the last 6 months, his information appears 10 times in the query results when I only need to identify it once.

Question: Is there a way force the query to produce only one instance (record) in the result eventhough there may be multiple records in the subform that satisfy the query.

 

[tdatgod]

Hi,
I have never used Brio so I don't know if there is a way to do this in brio. however

Some Databases allow the use of DISTINCT to remove duplicates from the answer set. This is if they are Ansi Compliant

Sel distinct name, address ....
from donerstable
where donerid in
( sel donorid from donationstable
where donationdate >
( current_date - interval '180' days ));

Or something like that. Maybe Brio has an Option somewhere to eliminate duplicates from the result set which tells it to use this option.

Hope this helps.

 

[jabrony76]

ezaccess,

In the results section of BRIO you can highlight the column containing the donor's name, right click and check "supress duplicates".

Another method of doing this would be run the query of all donors in the past 6 months, dump it into excel and used advanced filter to filter for unique records only.

Just my 2 cents..

Andy

 

[hutchkc]

ez access
you are running into the same problem that we have found. having multiples causes problems in our report. the suppressing of duplicates hides the duplicates in the results but still shows on the results. if you are looking to have only one row without the duplicates you are going to have to use what we are doing, which is to make a seperate query for just the information that you want in a single query.