How do I make each record show only once?

B

BFry

I am rusty with Access (it's been awhile). I have 2 tables, one with donor
contact information (MUSEUMCONTACT) and the other with donor gift information
(CONTRIBUTION). The tables are linked with a contact number. For each
record in the MUSEUMCONTACT table, there may be multiple gifts/records in the
CONTRIBUTION table.

I would like to create a query that shows me anyone who has gifts in the
CONTRIBUTION table, but I want to see their record only once no matter how
many gifts they have. Ultimately, I'd like to sum all the gifts for one
record, but that can be down the road. Can anyone help? Thank you.
 
A

Allen Browne

In query design view, depress the Total button on the toolbar (upper sigma
icon).
Access adds a Total row to the grid.

Under the fields from the MUSEUMCONTACT table, accept Group By in the Total
row.

In the fields from the CONTRIBUTION table, choose something else, e.g.:
- Where if you just want to specify critiera (not see a result), or
- Sum if you want to see the total amount.
 
B

BFry

Thank you for your post. I fear I need yet more basic help because I was
unable to make this work. The Contact ID is still showing up numerous times
(once for each record in the CONTRIBUTION table.

Not sure if I'm running the right kind of query or not - just have the
tables linked in a select query.

Ultimately, I just want each Contact ID #, where there are records in the
CONTRIBUTIONS table to show a total of the amount fields for each record that
bears the same Contact ID #. Does that make sense?

I should also mention I didn't build this database so am not really certain
how it works. Thanks so much!
 
B

BFry

Forgive me! My last reply was premature. I was able to make it show each
contact ID only once! Now - is there a way to sum every record in the
CONTRIBUTIONS table with the same Contact ID?

THANK YOU VERY MUCH!
 
C

Christine King

At the moment you will have every field set to "group". So it will! There are
other options here, so if you change this to "Sum" for your money field, it
will sum these. 2 things. 1) Has to be a numerical field to sum. If you want
to count the number of records, change "group" to "count" - BUT it will
ignore any rows where the field you're counting is blank. If you want all
records it's best to count on the primary key or link field.
 

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