Can't get rid of duplicates in an Access sub report

0
votes

I'm having an issue with a report containing a subreport in Access 2007. Let me preface this by saying that I didn't create this database, it was already in place and I've just kinda been put in the position of taking it over, and I don't know too much about access.

Basically I need my report to pull my customer history and payment history, group them based on the phone number, display all history below, and finally being sorted by received date.

What is happening though is I'm getting duplicates of all payment info.

Here's currently what is being output:

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

sold date   received date   amount   agentname
1/1/2013       1/4/2013       50       jack
6/1/2012       6/6/2012       25       jim

So as you can see, it is grouping the sales correctly on the phone number, as shown by the phone number, name, and address only being displayed once. However for each instance that the subreport pulls, it shows all the info again. I need only one set of sale information to display per customer.

I have two tables, two queries, and two reports doing all of this.

One table contains customer information, the other contains sale information.

The queries simply pull the needed info.

My main report has the phone number, name, and address inside the header section. This is set to group on phone number, and to keep the group together on one page.

My subreport contains all the sale info inside the detail section. This is set to sort by received date.

I've tried a ton of combinations of grouping, sorting, and moving the subreport in and out of the header and detail sections but to no avail. One way or another there's always duplicates and I can't figure out how to sort the whole shebang by received date when all is said and done. Any help would be greatly appreciated!

1
ms-accessms-access-2007
What does the query for your subreport look like? And is your subreport configured to group on something? – HK1
As far as the date sorting; make sure the date field is actually a DATE/TIME and not TEXT. If it is, make sure in the Sorting & Grouping section, it's sorted ASCENDING and not DESCENDING. – Johnny Bones
SELECT tblPledgesLead.PhoneNumber, tblPledgesLead.DispositionTime, tblPledgesLead.DateRecd, tblPledgesLead.PledgeAmountRecd, tblPledgesLead.Agent, tblPledgesLead.CampaignName, tblPledgesLead.Custom20 FROM tblPledgesLead WHERE (((tblPledgesLead.DateRecd)>#1/1/2009#) AND ((tblPledgesLead.PledgeAmountRecd)>0)); – epilimic
Yes my date field is time/date, and is set to sort from newest to oldest since i want the most recent to appear on top. The subreport sorting seems fine as you can see in my example output above. The sorting issue that i'm having though is more to do with the report as a whole needs to be sorted based on the most recent sales dates. – epilimic
bump, does anybody know what else I can try? – epilimic

1 Answers

0
votes

Create a phone number group section and move the top line with phone number, name, and address AND the sales information subreport inside this new group header. Then, minimize the detail section.

From your example, John Smith has two sales info records. Therefore, the sub report placed in the detail section will repeat twice for its two appearances in the main report. Recall any item in detail section will repeat for the number in the recordsource. Yet positioning items in a distinct group section will remove the duplication.