Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    11

    Arrow Unanswered: Need help sorting my report by date.

    Hello, 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:
    Code:
    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

    Attached is a stripped down copy of my database. Any help with this would be greatly appreciated. It's so close to being done I can taste it :P

    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.
    Attached Files Attached Files
    Last edited by flipe; 10-04-13 at 03:57.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its hard to tell from your sampole data what your problem is, especially as the data int he 'before and after" don't tally. however what I suspect you are asking is that your query should display the most recent donors first. if so:-

    so the first thing to do is to write a query which finds the mast recent date for each telephone number
    Code:
    SELECT tblPledgesLead.PhoneNumber, max(dateRecd) as MostRecentDate
    FROM tblPledgesLead
    GROUP BY tblPledgesLead.PhoneNumber;
    save that query, say as qryPhoneNumbersinPledgeOrder

    juut in case it goes wrong copy your current query 1new to soemthing else
    then open 1 new for edit
    switch to SQL view (the view icon under the fiel menu)
    then paste the following
    Code:
    SELECT qryPhoneNumbersinPledgeOrder.PhoneNumber, [FirstName] & [lastname] AS Contributor, tblPledgesLead.DateRecd, tblPledgesLead.PledgeAmountRecd, tblPledgesLead.PhoneNumber, tblContributorsLead.FirstName, tblContributorsLead.LastName, tblContributorsLead.Address1, tblContributorsLead.ZipCode, tblContributorsLead.CityName, tblPledgesLead.PledgeAmountRecd
    FROM (qryPhoneNumbersinPledgeOrder INNER JOIN tblContributorsLead ON qryPhoneNumbersinPledgeOrder.PhoneNumber = tblContributorsLead.PhoneNumber) INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber
    ORDER BY qryPhoneNumbersinPledgeOrder.PhoneNumber, tblPledgesLead.DateRecd DESC;
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    11
    I did that but it's still out of order. Sorry I tried to be as clear as possible in my examples above. The important part to look at is the dates. My current query pulls all the right info, so isn't this just a matter of having the report sort it the right way?

    Currently this is my output and it is and incorrect:

    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


    And now this is the order in which they need to be output as.

    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

    It is also important that each contact listing's sales remain in order with the most recent date first as well. Such as:

    Hann Solo
    5552223434
    $20 9/18/2013 <--first
    $20 1/22/2013 <--second
    $15 7/6/2012 <-- third
    $10 1/4/2012 <--fourth
    Last edited by flipe; 10-04-13 at 09:31.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry youve lost me.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2013
    Posts
    11
    Nevermind. Somebody on stackoverflow was able to understand it and got this completely sorted out. Thank you very much!
    Last edited by flipe; 10-04-13 at 10:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •