By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,044 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,044 IT Pros & Developers. It's quick & easy.

@#$@% report! Data order issue... simple data order issue.

P: 39
I'm having a frustrating issue with a report that WON'T order my data properly, seemingly whatever I do.
Surely I must be overlooking something!

I have an "order by" in my raw query:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ViewInventoryStatus.CustPN, 
  2. ViewInventoryStatus.MfgPN, ViewInventoryStatus.Qty, 
  3. ViewInventoryStatus.Manufacturer, 
  4. ViewInventoryStatus.Description, 
  5. ViewInventoryStatus.PartsOwner, 
  6. ViewInventoryStatus.PackageType
  7. FROM ViewInventoryStatus
  8. ORDER BY CustPN; 
I built a report on this query, and the data is definitely NOT ordered by CustPN.

So then I went into the report-level Properties - and on the data tab set the Order By property to "CustPN", and set the Order By On property to "yes".

And the data is definitely NOT ordered by CustPN.

What the #$#@ is going on?
I'm going to be the next viral video of "man in office throws laptop through window"...

Most seriously - wtf?
Sep 22 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Hi geolemon. You are understandably confused about this - report ordering is not dependent in any way on the ordering of fields in the underlying query.

The order of the records in a report is dependent on the sorting and grouping applied within the report itself.

Open your report in design view, then select view, sorting and grouping to open the sort/group dialogue. From here you can apply grouping (including the settings for group headers and footers in your report, and keep-together settings for maintaining the integrity of grouped elements). The sort order is hierarchical from the first item you define downwards, whether or not you have group headers and so on defined. You can sort records ascending or descending on any of the fields listed.

It is because the ordering is entirely independent of the order of the underlying query that the report wizard asks you whether you want to sort the fields, and what the grouping is. Using the wizard is a good place to start, as is looking at reports in the sample Northwind database that comes with Access, to see how they work.

Access is not really clear about the difference between report ordering and query ordering - but think of what you have learned in trying to resolve it...

Sep 22 '08 #2

Expert Mod 15k+
P: 31,761
GeoLemon, Switching to Access from other RDBMSs can be an awkward process. Don't panic. There is generally sense hidden around there somewhere :)
Sep 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.