Show Duplicate records only once


L

LiseD

I am trying to do the following and I can't figure this out - not alot of
experience with Access.

I have a query that combines two tables, one for donors and one for
donations that has a one to many relationship. I would like to create a
query to show the donors that meet a certain criteria based on the donations,
i.e. >60 from one field and >250 from another but it pulls all of the
donations that meet this criteria for each donor. Some have only donated
once and some several times. I only want to show the donors that meet this
criteria once.

I would also like to calculate the percentage of these donors that meet this
criteria to the total number of donors in the database.
 
Ad

Advertisements

G

Golfinray

Without doing sql, try hitting the addition key (the little E at the top)
which will change everything to group by. Then instead of groupby, change the
dollar field to Max. If that works, we'll try the percentage.
 
J

John W. Vinson

I am trying to do the following and I can't figure this out - not alot of
experience with Access.

I have a query that combines two tables, one for donors and one for
donations that has a one to many relationship. I would like to create a
query to show the donors that meet a certain criteria based on the donations,
i.e. >60 from one field and >250 from another but it pulls all of the
donations that meet this criteria for each donor. Some have only donated
once and some several times. I only want to show the donors that meet this
criteria once.

Uncheck the "Show" checkbox on the field (or fields) from the Donations table,
and rightclick the background of the tables in the design window and choose
Properties; check the "Unique Values" option.
I would also like to calculate the percentage of these donors that meet this
criteria to the total number of donors in the database.

Make it a totals query, count the records in this query, and divide that sum
by DCount("*", "[Donors]")
 
Ad

Advertisements

L

LiseD

That works great. For some reason I thought it was more complicated than
that. Thank you. I acually had it calculate the percentage at the end of
the report.

John W. Vinson said:
I am trying to do the following and I can't figure this out - not alot of
experience with Access.

I have a query that combines two tables, one for donors and one for
donations that has a one to many relationship. I would like to create a
query to show the donors that meet a certain criteria based on the donations,
i.e. >60 from one field and >250 from another but it pulls all of the
donations that meet this criteria for each donor. Some have only donated
once and some several times. I only want to show the donors that meet this
criteria once.

Uncheck the "Show" checkbox on the field (or fields) from the Donations table,
and rightclick the background of the tables in the design window and choose
Properties; check the "Unique Values" option.
I would also like to calculate the percentage of these donors that meet this
criteria to the total number of donors in the database.

Make it a totals query, count the records in this query, and divide that sum
by DCount("*", "[Donors]")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top