Duplicate instances of records flooding my Access report

[flipe]

I'm having a duplication issue that I just can't figure out. Basically my report & subreport are pulling a previous contributor list. What I need is for each instance of paid history to be grouped by the phone number and then the whole shebang sorted by date of the last sale. This is mostly happening except the problem is that for every instance of the sale, I am getting the full info returned. This is causing a lot of duplicates as you can imagine with the 10,000+ customers we have.


So to be clear, in my example below I am getting the full phone number, name/address, and both sales in both places (based on date) in my report. I will see all of the info on both 1/4/2013 AND 6/6/2012. I don't want or need the instance at 6/6/2012 to show up, I only want it to show up alongside the 1/4/2013 sales. It still needs to have the 6/6/2012 paid history there, I just need the 2nd instance of it all to be eradicated.

    555-1212
    john smith
    123 whatever dr
    hometown, usa 90210
    
    sold date   received date   amount   agentname
    1/1/2013       1/4/2013       50       jack
    6/1/2012       6/6/2012       25       jim
    


    555-1212
    john smith
    123 whatever dr
    hometown, usa 90210
    
    sold date   received date   amount   agentname
    1/1/2013       1/4/2013       50       jack
    6/1/2012       6/6/2012       25       jim

My report is setup so the phone number, name, and address are all inside the PhoneNumber header and my subreport which contains the sold/received dates, amount, and agentnames is inside the detail section.


Here's the query for my main report:


SELECT DISTINCT
tblContributorsLead.PhoneNumber, tblContributorsLead.FirstName,
tblContributorsLead.LastName, tblContributorsLead.Address1,
tblContributorsLead.ZipCode, tblContributorsLead.CityName,
tblPledgesLead.PledgeAmountRecd, tblPledgesLead.DateRecd
FROM
tblContributorsLead
INNER JOIN tblPledgesLead
ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber
WHERE
(((tblPledgesLead.PledgeAmountRecd)>0)
AND ((tblPledgesLead.DateRecd) Is Not Null))
ORDER BY
tblPledgesLead.DateRecd DESC;


Here's the query for my subreport:


SELECT
tblPledgesLead.PhoneNumber, tblPledgesLead.DispositionTime,
tblPledgesLead.DateRecd, tblPledgesLead.PledgeAmountRecd,
tblPledgesLead.Agent, tblPledgesLead.CampaignName,
tblPledgesLead.Custom20
FROM
tblPledgesLead
WHERE
(((tblPledgesLead.PledgeAmountRecd)>0));


I'd also like to say that this system was already in place before I took control, and I'm relatively new with access so any help will be greatly appreciated. I've moved every piece of this thing into and out of every section I can see and have tried a multitude of grouping and sorting options but to no avail.


Here's a couple screenshots showing the issue. In the first screenshot I have circled a particular contributor. Notice the Date Rec'd column on each sale, that is the date they are being sorted by. The place that this contributor is at currently is where it should be, alongside the other sales from 9/23/2013. In the second screenshot you can see her full info listed again at the location of her previous sale, on 12/14/2010. This continues on for each and every date she has ever paid. This sale in particular will have 4 copies instead of just the one that I need. The one that I need should look identical to the one from 9/23/2013, containing all prior sale info alongside.




First: http://i.imgur.com/5uRmyJ0.jpg


Second: http://i.imgur.com/APmEd2V.jpg

[June7]

This is usually result of including multiple tables in query where each has a 'many' relationship to a parent table. Not seeing that with the posted queries and I can't view your images now. Will probably need to analyse the report directly if you want to provide. Follow instructions at bottom of my post.

[flipe]

Alright so I was able to get a little help elsewhere but the issue isn't fully solved. Let's start anew from where I am now:

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:

	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.

[Dal Jeanis]

You only want one Lastdate per person, so use "GROUP BY PhoneNumber" and "MAX([DateRecd])" in your second select.

SELECT 
   SortingAndGrouping.LastDate, 
   SortingAndGrouping.PhoneNumber, 
   tblPledgesLead.DateRecd
FROM 
   (tblContributorsLead INNER JOIN tblPledgesLead 
    ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber) 
INNER JOIN 
   (SELECT 
       CDate(Format(Nz(Max([DateRecd]),#1/1/9999#),"MM/DD/YYYY")) AS LastDate,
       tblPledgesLead.PhoneNumber 
    FROM 
       tblContributorsLead INNER JOIN tblPledgesLead 
       ON tblContributorsLead.PhoneNumber=tblPledgesLead.PhoneNumber 
    GROUP BY 
       tblPledgesLead.PhoneNumber) AS SortingAndGrouping 
ON tblContributorsLead.PhoneNumber = SortingAndGrouping.PhoneNumber
ORDER BY 
   SortingAndGrouping.LastDate DESC , 
   SortingAndGrouping.PhoneNumber, 
   tblPledgesLead.DateRecd DESC;

I generally prefer to use shorter aliases, rather than longer ones, and to alias each table that appears twice in two different ways, to avoid confusion.
That would look like this:

SELECT 
   TS.LastDate, 
   TS.PhoneNumber, 
   TP.DateRecd
FROM 
   (tblContributorsLead AS TC INNER JOIN tblPledgesLead AS TP
    ON TC.PhoneNumber = TP.PhoneNumber) 
INNER JOIN 
   (SELECT 
       CDate(Format(Nz(Max([DateRecd]),#1/1/9999#),"MM/DD/YYYY")) AS LastDate,
       TP2.PhoneNumber 
    FROM 
       tblContributorsLead AS TC2 INNER JOIN tblPledgesLead AS TP2
       ON TC2.PhoneNumber=TP2.PhoneNumber 
    GROUP BY TP2.PhoneNumber) AS TS
  ON TC.PhoneNumber = TS.PhoneNumber
ORDER BY 
   TS.LastDate DESC , 
   TS.PhoneNumber, 
   TP.DateRecd DESC;

That change allowed me to notice that the tables aliased as TC and TC2 don't appear to be doing anything in this query.

Eliminating them resulted in this simpler query:

SELECT 
   TP2.LastDate, 
   TP.PhoneNumber, 
   TP.DateRecd
FROM 
    tblPledgesLead AS TP
INNER JOIN 
   (SELECT 
       CDate(Format(Nz(Max([DateRecd]),#1/1/9999#),"MM/DD/YYYY")) AS LastDate,
       TP2.PhoneNumber 
    FROM 
       tblPledgesLead AS TP2
    GROUP BY TP2.PhoneNumber)
ON TP.PhoneNumber = TP2.PhoneNumber
ORDER BY 
   TP2.LastDate DESC , 
   TP2.PhoneNumber, 
   TP.DateRecd DESC;