Need to sort access report by date

Question

I've got a report that I'm trying to sort but really have no idea how. I've tried a number of things but nothing is working.

Here's my query:

SELECT [FirstName] & " " & [LastName] AS Contributor, PledgeAmountRecd, tblPledgesLead.PhoneNumber, FirstName, LastName, Address1, CityName, ZipCode, DateRecd, CCur([PledgeAmountRecd]) AS Pledge
FROM tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber=tblPledgesLead.PhoneNumber
ORDER BY [FirstName] & " " & [LastName], tblPledgesLead.DateRecd DESC;

Basically I need the report to sort each contributor based on the most recent payment. Notice in this example output that for each contributor there is a list of payment history which is sorted from most recent to the oldest. Overall, each contributor is then in order of the most recent paid date; i.e. everyone with the most recent paid date of 9/19/2013 will be together, then as it goes down we get to 9/18/2013 as a most recent date, and so on.

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/18/2013
$15 3/1/2013
$10 11/15/2012

Hann Solo
5552223434
$20 9/18/2013
$20 1/22/2013
$15 7/6/2012
$10 1/4/2012

Currently it is outputting like this:

Tom Smith
5552221111
$20 8/23/2013

Jim Jones
5552223333
$20 9/17/2013
$15 4/5/2013

Joe Blow
5552229999
$20 9/4/2013
$20 3/1/2013

Hector Gonzales
5552228888
$15 8/29/2013

As you can see in the current output, these are way out of order. In the end they should look like the first example posted above.

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

score 1 · Accepted Answer · answered Oct 04 '13 at 09:59

1

Here is how I would approach it:

I'd create a Report called [Contribution_subreport] based on the [tblPledgesLead] table. It would display the [PledgeAmountRecd] and [DateRecd], sorted by [DateRecd] descending (most recent first).

Then I'd create a Report called [Contribution_report]. It's Record Source would be the query...

SELECT 
    tblContributorsLead.PhoneNumber, 
    Max([tblContributorsLead].[FirstName] & " " & [tblContributorsLead].[LastName]) AS Contributor, 
    Max(tblPledgesLead.DateRecd) AS MaxOfDateRecd 
FROM 
    tblContributorsLead 
    INNER JOIN 
    tblPledgesLead 
        ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber 
GROUP BY tblContributorsLead.PhoneNumber;

...and it would contain three controls:

Text box: [Contributor]
Text box: [PhoneNumber]
Subreport: [Contributions_subreport]

The subreport would be linked to the main report by [PhoneNumber]...

...and be sorted by [MaxOfDateRecd] (descending) and [Contributor]...

The final report would look like this:

answered Oct 04 '13 at 09:59

Gord Thompson

116,920
32
215
418

@epilimic You can download a working example [here](http://wikisend.com/download/618720/Contrib.zip). – Gord Thompson Oct 04 '13 at 10:09
That looks absolutely perfect. The output is just how I'd like. See, the database that I'm working with is an old one that was in place already, but had problems. It has a broken version that is similar to this, with a subreport inside. It's issue is that the outputs had duplicates, as in another full copy of the contact and all sale history at EACH date that was listed, and it was making my report a disaster. I posted around to a couple forums and a couple people questioned why I didn't just do it with a single one. So I basically tried to recreate what was needed. Let me give this a try. TY! – epilimic Oct 04 '13 at 13:45
Perfect! I applied this to my main database and it works exactly like I need it. You've a life saver Gord! I'm very new with access so trying to wrap my head around all these different pieces and how they interact with each other has been challenging. You made it very clear though in your answer and I greatly appreciate that. I've been banging my head against a wall trying everything I could think of for a few days now. +++ – epilimic Oct 04 '13 at 13:58

score 0 · Answer 2 · answered Oct 04 '13 at 07:24

    SELECT [FirstName] & " " & [LastName] AS Contributor, PledgeAmountRecd,
           tblPledgesLead.PhoneNumber, FirstName, LastName, Address1, CityName,
           ZipCode, DateRecd, CCur([PledgeAmountRecd]) AS Pledge
    FROM tblContributorsLead 
         INNER JOIN tblPledgesLead 
                    ON tblContributorsLead.PhoneNumber=tblPledgesLead.PhoneNumber
    ORDER BY tblPledgesLead.DateRecd, Contributor DESC;

I hope this works :)

Need to sort access report by date

2 Answers2