Alphabetic Order

[sandhurstUK]

The following code returns the data I need, but places the 'Totals' within the list. How can I force the code to place the Total at the end, ie after the last Z entry?

Select Targets.Surveyor, Targets.[2002 Target], Targets.SS, Targets.MAP, Targets OOA
From Targets
Order by Targets.Surveyor


Many thanks
Ian

 

[glynch]

It is not apparent from your query what would differentiate a "Totals" record from a regular data record, but you could always add another field, enter "zzzzzzzz" in that field for the "Totals" record and sort first on that new field and then on Surveyor.

Order by Targets.NewField, Targets.Surveyor

HTH

 

[sandhurstUK]

Thanks glynch,

The data comes down from an Excel spreadsheet which contains a list of surveyors with the 'total' being the sum of each spreadsheet column to which it is associated. Perhaps a solution would be not to import the totals, then got Access to calculate them for me, but I still am not sure how to make the TOTALS, display at the bottom of the datasheet rather than before those surveyors with name beginning with W and Z ( incidentally - of which I have 2).

Regards
Ian

 

[sandhurstUK]

Thanks glynch,

The data comes down from an Excel spreadsheet which contains a list of surveyors with the 'total' being the sum of each spreadsheet column to which it is associated. Perhaps a solution would be not to import the totals, then got Access to calculate them for me, but I still am not sure how to make the TOTALS, display at the bottom of the datasheet rather than before those surveyors with name beginning with W and Z ( incidentally - of which I have 2).

Regards
Ian

 

[RV]

>Perhaps a solution would be not to import the totals, then got Access to calculate them for me<

That's right, don't import the totals.
It's bad practice to store calculated data in database tables, just calculate them whenever needed.

>I still am not sure how to make the TOTALS, display at the bottom of the datasheet rather than before those surveyors with name beginning with W and Z ( incidentally - of which I have 2)<

Don't think in datasheets in Access as Access doesn't know datasheets....
Post an example of what you're trying to accomplish in Access.

RV

 

[sandhurstUK]

Sorry RV, been away for a couple of days.

My data is displayed in a table type format using the display setting of datasheet view. This is in fact a subform which sits on a main form called targets. It displays a tabulated list of surveyors and their workloadings, however, two records before the end, the Totals record appears because of the query being set to display sorted A-Z. What I may have to do as previously mentioned is remove the calculated total, and create another subform underneath which displays the Totals.

Thanks anyway
Ian

 

[Jon K]

Try this Order By clause in the query:-

ORDER BY iif(Surveyor="Totals",1,0), Surveyor