CODEWITHSUNDEEP
ms-accessms-access-2007
epilimic
epilimic

Reputation: 27

Need to sort access report by date

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

Upvotes: 1

Views: 1958

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123829

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]...

subreport.png

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

sort.png

The final report would look like this:

result.png

Upvotes: 1

Tauseef
Tauseef

Reputation: 2052

    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 :)

Upvotes: 0

Related Questions