Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Suppress duplicate records (2003 SP3 standalone)

    I have two tables with a one-to-many relationship:

    tbl Contacts...............tbl Contributions
    ID................................ID
    Last Name................Contrib ($)
    First Name................Date
    Address,etc..............Contrib ($)
    Contributor (y/n).......Date

    One person can make many contributions. I have a Select qry which joins the two tables on the ID field. If I set the criteria for tblContacts.Contributor to True, I show all contributors. But, some people are marked True so that they will get our mailings even though they have given no money.
    Q1: How do I show only one record for each Contributor, independent of how many times he has given? I can use a Query or a Report. I have seen <post:=317,809>post 317,809</post:> but that doesn't work for me.
    Q2: How do I eliminate the people who didn't give a contribution?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    What exactly is your purpose? Do you want to return only contact data or contact AND contribution data?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    Only Contact data. I have a query to return contact AND contribution data. That's easy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    Create a query based on the contacts table only.
    To select contacts that have at least one corresponding record in the contributions table, set the criteria for the ID field to

    In (SELECT ID FROM [tbl Contributions])

    To select contacts without any corresponding record in the contributions table, set the criteria for ID to

    Not In (SELECT ID FROM [tbl Contributions])

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    That doesn't work. I get a parameter dialog that asks me for a value for ID. How can a single table query ask for data in another table?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    I can't do that right now, but I can tell you that I just created a two-table Totals query that totals each contact's contributions as well as the count of each persons contributions. Here is the SQL:

    SELECT Contacts.ContactID, Sum(tblContributions.Contribution) AS SumOfContribution, Contacts.LastName, Contacts.FirstName, Count(Contacts.LastName) AS CountOfLastName, Contacts.CompanyName
    FROM Contacts INNER JOIN tblContributions ON Contacts.ContactID = tblContributions.ContactID
    GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.CompanyName
    ORDER BY Sum(tblContributions.Contribution) DESC;

    This was a somewhat circuitous way of eliminating duplicate contributors. It gives me the 882 people who have contributed at least once, out of the 953 Contacts with Contributor=True, but no $.
    I got the number 953 from my old database, before it was split into two tables. You helped me with the normalization of the old db several months ago. (I will look up that post later. It was the one where I noticed that you were in the Euro Zone.)
    Bottom line: there are only two significant tables, tblContacts and tblContributions, which I described in my original post.
    What I need now is a query that finds records where Contacts.Contributor=True AND have no entry in tblContributions.
    Thanks for all your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    You can use a query with a left join for this:

    SELECT Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.CompanyName
    FROM Contacts LEFT JOIN tblContributions ON Contacts.ContactID = tblContributions.ContactID
    WHERE Contacts.Contributor=True AND tblContributions.ContactID Is Null

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Suppress duplicate records (2003 SP3 standalone)

    That's it! I forgot about the left join.
    Thanks again,
    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •