Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2013
    Posts
    11

    Exclamation Unanswered: access 2007 query producing duplicate results

    Hello, this stemmed from another issue which was mostly solved. Basically I have a query that pulls info from two tables. One table is a list of contacts and the other is a list of payments. I have a report which will take those results and group the payment history together based on the phonenumber field. Well this is happening correctly and everything is sorted properly.. however the problem is that it is pulling duplicate instances for each paid date. What I mean by this is that say John Smith paid on 1/1/2013 and again on 6/6/2013, I am getting both results showing up for both of those dates. This is flooding my report with duplicates and I need them to not show up at all.

    The end result should be only 1 instance of each customer lead with all of their paid history grouped below, and sorted in order of the most recent paid date per customer.

    Here's a stripped down copy of my database: http://icloudbackups.com/s.zip

    Here's my query:
    Code:
        SELECT 
            SortingAndGrouping.LastDate, 
            SortingAndGrouping.PhoneNumber, 
            tblPledgesLead.DateRecd
        FROM 
            (tblContributorsLead 
            INNER JOIN tblPledgesLead 
                ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber) 
            INNER JOIN (SELECT CDate(Format(Nz([DateRecd],#1/1/9999#),"MM/DD/YYYY")) 
                AS LastDate, tblPledgesLead.PhoneNumber 
                    FROM tblContributorsLead 
            INNER JOIN tblPledgesLead 
                ON tblContributorsLead.PhoneNumber=tblPledgesLead.PhoneNumber 
                    ORDER BY tblPledgesLead.DateRecd DESC)  
                        AS SortingAndGrouping 
                            ON tblContributorsLead.PhoneNumber = SortingAndGrouping.PhoneNumber
            ORDER BY SortingAndGrouping.LastDate DESC , 
                SortingAndGrouping.PhoneNumber, 
                tblPledgesLead.DateRecd DESC;
    If you run the query and search the number 5552542995 you'll see the duplicates that I'm referring to. There will be two entries alongside the dates 9/19/2013 and 8/9/2013. This contributor should only show up with his two paid histories alongside the sales of 9/19/2013.

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Make a copy of your original qry and use this as it's record source. See if this is what you want.
    SELECT TOP 1 SortingAndGrouping.LastDate, tblPledgesLead.PhoneNumber, tblPledgesLead.DateRecd
    FROM (tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber) INNER JOIN (SELECT CDate(Format(Nz([DateRecd],#1/1/9999#),"MM/DD/YYYY")) AS LastDate, tblPledgesLead.PhoneNumber FROM tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber=tblPledgesLead.Pho neNumber ORDER BY tblPledgesLead.DateRecd DESC) AS SortingAndGrouping ON tblContributorsLead.PhoneNumber = SortingAndGrouping.PhoneNumber
    GROUP BY SortingAndGrouping.LastDate, tblPledgesLead.PhoneNumber, tblPledgesLead.DateRecd
    HAVING (((Max(SortingAndGrouping.PhoneNumber))="555254299 5"))
    ORDER BY SortingAndGrouping.LastDate DESC , tblPledgesLead.DateRecd DESC , Max(SortingAndGrouping.PhoneNumber);

  3. #3
    Join Date
    Sep 2013
    Posts
    11
    Not really. That only brings up a single result from 9/19/2013. I need all sale history grouped together under just a single contributor record, based on phone number.

    Example of the output I need:

    Amanda Hugginkiss
    5552224321
    $50 9/19/2013

    Johnny Appleseed
    5552221234
    $20 9/19/2013
    $15 8/9/2013

    Jake Thesnake
    5552229876
    $20 9/19/2013

    Moe Noe
    5552226789
    $10 9/19/2013
    $15 3/1/2013
    $10 11/15/2012

    Notice that all 4 of these records have their most recent paid date on 9/19/2013, and that they are all grouped together under the contact name and phone number.

    The issue that I'm having is, for those contacts who have paid more than once, I'm seeing a full record listing at EACH of those dates. From my example above, Johnny Apppleseed appears with both of his paid entries at two locations in my report. Once at 9/19/2013 and again at 8/9/2013.

    Here's an example of how it is incorrectly appearing in my query/report:

    Joe Schmoe
    5552225151
    $20 8/9/2013

    Vicki Valencourt
    5552221515
    $10 8/9/2013
    $10 2/17/2013

    Johnny Appleseed
    5552221234
    $20 9/19/2013
    $15 8/9/2013

    Tony Twotimes
    5552228989
    $20 8/9/2013

    So as I have hopefully illustrated properly, I am getting a complete duplicate record for every contact that has paid multiple times. I only need to see Johnny Appleseed with his two paid instances listed alongside the sales at 9/19/2013. The one at 8/9/2013 needs to be hidden.

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    SELECT [tblContributorsLead]![FirstName] & " " & [tblContributorsLead]![LastName] AS Contributor, tblPledgesLead.DateRecd, tblPledgesLead.PhoneNumber, CCur([PledgeAmountRecd]) AS Pledge
    FROM tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber
    ORDER BY [tblContributorsLead]![FirstName] & " " & [tblContributorsLead]![LastName], tblPledgesLead.DateRecd DESC;

  5. #5
    Join Date
    Sep 2013
    Posts
    11
    Ok that looks good for the query, but the sorting of my report is thrown off now. How do I need to set my grouping and sorting options to have them sorted based on the most recent paid date?

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    For your Report, DateRecvd is the Paid Date correct? Do you want that date sorted by Day,Week,Month or what?

  7. #7
    Join Date
    Sep 2013
    Posts
    11
    Yes, DateRecd is the paid date. Needs to be sorted by day like in this example:

    Amanda Hugginkiss
    5552224321
    $50 9/19/2013

    Johnny Appleseed
    5552221234
    $20 9/19/2013
    $15 8/9/2013

    Jake Thesnake
    5552229876
    $20 9/19/2013

    Moe Noe
    5552226789
    $10 9/19/2013
    $15 3/1/2013
    $10 11/15/2012

    The individual contacts' paid dates should be sorted in order of most recent first, and overall I need the entire report to have them in order based on the most recent paid date.

  8. #8
    Join Date
    Nov 2011
    Posts
    413

    Query Producing Duplicate Records

    Here is a example db. Don't know if this is exactly what you want but should be enough to get you started.

    Good Luck With Your Project.
    Attached Files Attached Files

  9. #9
    Join Date
    Sep 2013
    Posts
    11
    Your dates are still all out of order there, I don't see what has changed other than a new report format. I need mine to stay as-is because I'm printing on 3x5 cards.

    Here's my database in it's current form:
    http://icloudbackups.com/s2.zip

    The only thing left is to have them sorted in order of the most recent DateRecd.

  10. #10
    Join Date
    Nov 2011
    Posts
    413
    I fixed the qry for you but was not aware of your Report criteria about the cards and have no way of checking that anyway. This criteria was not explained from the beginning. You should be able to design your own Report from here.

    Good Luck!

  11. #11
    Join Date
    Sep 2013
    Posts
    11
    Quote Originally Posted by Burrina View Post
    I fixed the qry for you but was not aware of your Report criteria about the cards and have no way of checking that anyway. This criteria was not explained from the beginning. You should be able to design your own Report from here.

    Good Luck!
    Yeah sorry it was a two stemmed problem apparently. I assumed that fixing the query would fix the report also. Sorry I am new with access.

    I guess I'll make a new thread. Your query is pulling the right info, I just don't know how to sort it.

  12. #12
    Join Date
    Nov 2011
    Posts
    413

    Query Producing Duplicate Records

    Final Version.Your on your own after this. FYI, your db is a mess, duplicate field names that conflict with each other in related tables.Research naming conventions and db design a little. It will help you a lot in the long run.
    Attached Files Attached Files

  13. #13
    Join Date
    Sep 2013
    Posts
    11
    That is still not sorted, I don't see what you changed again.

    I also don't see what you mean by duplicate field names. The only one used more than once is PhoneNumber.

    This database was setup so that one table has just one record of each customer, and the other table has payment history. The two can then be linked on the PhoneNumber field.

    Thank you for the help with the query.

  14. #14
    Join Date
    Nov 2011
    Posts
    413
    tblContributorsLead and tblPledgesLead both have:
    FirstName
    LastName
    Address1
    Address2
    CityName
    StateName
    CountryName
    etc.. in Common and as a result you have to state which table to use in your qry.
    The DateRecd WAS sorted from Newest to Oldest in YOUR Report named rptAllReceipts.
    It is Grouped by PhoneNumber.
    As I said, it's up to you now.
    FINAL POST

  15. #15
    Join Date
    Sep 2013
    Posts
    11
    Ok I see what you mean. I didn't realize there were other fields there since they weren't being shown.

    Regardless, I removed them and don't see any change.

    My DateRecd is sorted properly only for the individual payments. My contributors have never been sorted properly overall.

    Again I thank you but I've moved on and created a new thread since this is now a new problem. Apologies once again for being very new with access. This was a project that was just dropped on my lap and I have no idea what I'm doing. I'm trying my absolute best though and it's honestly discouraging to have you repeatedly tell me that I'm on my own. The entire reason for me being on these forums is because "on my own" means that I'm clueless and I'm reaching out for help. You can choose to not help at all and that's fine, but I'd much rather you not even reply to begin with than keep reiterating that it is your FINAL POST to me on the subject. Sure you got me started but I have NO clue what I need to do now. So yes I thank you for fixing the query but I'm still stuck in a rut and am in need of help. I'm willing to learn if you can teach me what I'm doing wrong but there's no reason to be condescending about it.

    I don't know what report "rptAllReceipts" you're referring to is, but I did have an old version that had everything sorted in the right order but it had a duplicate contact info and duplicate payment info listed at every instance of a paid date.

    So my problem has either been a sorting issue or a duplication issue. At no point have both things been resolved together. My query was wrong before so I asked for help on it. Now that the query is fixed I assumed the report would work. I see now that it doesn't so I just need help getting the sorting to work again.

    Just lock this thread and let me move on to the next one.
    Last edited by flipe; 10-04-13 at 04:05.

Tags for this Thread

Posting Permissions

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