Duplicate instances of records flooding my Access report

faesce

Registered User.
Local time
Today, 00:20
Joined
Feb 16, 2012
Messages
22
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.

Code:
    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 DISTINCTROW
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.

edit: 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
 
Last edited:

Users who are viewing this thread

Back
Top Bottom