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

[geolemon]

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:

SELECT DISTINCT ViewInventoryStatus.CustPN, 
ViewInventoryStatus.MfgPN, ViewInventoryStatus.Qty, 
ViewInventoryStatus.Manufacturer, 
ViewInventoryStatus.Description, 
ViewInventoryStatus.PartsOwner, 
ViewInventoryStatus.PackageType
FROM ViewInventoryStatus
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?

[Stewart Ross]

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

-Stewart

[NeoPa]

GeoLemon, Switching to Access from other RDBMSs can be an awkward process. Don't panic. There is generally sense hidden around there somewhere :)