Databases
StackOverflow2013
Postsdbo
POMs Access 2007 - Can sort or group report but not both, can't figure out why
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMs Access 2007 - Can sort or group report but not both, can't figure out why
    primarykey
    data
    text
    <p>I'm stuck and confused and hopefully can get some help here. I have a query that pulls info from two tables and a report that reads it. My two tables are as such: One of the is a Contact list with phone numbers, names, and addresses. The other table is a paid history. The PhoneNumber field is how they're tied together. There are no duplicate entries in the Contact table but there are multiple paid instances per phone number in the other table.</p> <p>My report groups them on the phone number, but I also need it to sort by date. My date field is marked as time/date, and it is in the paid table. The issue I'm running into is that I can either Group on PhoneNumber OR sort on the Date field but not both. When I set the Group as the top level, it ignores the Sort that I have set below it. If I take the sort and drag it up so that it becomes the top level, it won't group. When it doesn't group I'm left with multiple instances of the same Contact info... as in I get a new listing for every date that it has ever paid, whereas I need one a single Contact listing with each paid instance to be grouped underneath it.</p> <p>Here's my query SQL:</p> <pre><code>SELECT tblContributorsLead.FirstName, tblContributorsLead.LastName, tblContributorsLead.Address1, tblContributorsLead.ZipCode, tblContributorsLead.CityName, tblPledgesLead.PledgeAmountRecd, tblPledgesLead.DateRecd, tblPledgesLead.PhoneNumber, tblPledgesLead.DispositionTime, tblPledgesLead.Agent, tblPledgesLead.CampaignName, tblPledgesLead.Custom20 FROM tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber WHERE (((tblPledgesLead.PledgeAmountRecd)&gt;0) AND ((tblPledgesLead.DateRecd) Is Not Null)); </code></pre> <p>Why would I only be able to either group OR sort but not both at the same time?</p> <p>Edit: <a href="http://icloudbackups.com/stripped.zip" rel="nofollow">http://icloudbackups.com/stripped.zip</a> is a copy of my database stripped down.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USepilimic
    1. USepilimic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COSo you want all of the payment information for each Contact to appear together, but you want the Contacts themselves sorted by the date of their most recent payment. Is that correct?
      singulars
      1. POMs Access 2007 - Can sort or group report but not both, can't figure out why
      1. USGord Thompson
    2. COCorrect. It needs to be in order based on their most recent payment.
      singulars
      1. POMs Access 2007 - Can sort or group report but not both, can't figure out why
      1. USepilimic
    3. COThere is no point sorting in the source query of the report. Reports do their own sorting.
      singulars
      1. POMs Access 2007 - Can sort or group report but not both, can't figure out why
      1. USPatrick Honorez
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload