From: neil40 on
In a League Table report I have the following layout
Season Header - Group on 'Season' (EG 1948)
League Header - Group on 'League' (A to Z)
Detail - Sort by 'League Position'

In the table, the League name comes from a separate table and on the
Input form is available from a Drop-down control that is an after
event of picking the season 'year'

Leagues are ranked in the real world by importance (Senior teams,
junior teams, trainee teams etc)
I input data each year in that order

My problem - I want to sort the Leagues such that the report outputs
in the order I have added them to the table (is this not a wise
strategy?) rather than the Alpha A-Z sort
For example
for a particular year, I might have
Natoinal League 1
National League 2
Metropolitain League

This is their order of seniority, but at present the Metropolitain
League is printing as the first (as letter 'M' is ranking above 'N'

What's the best strategy to remedy this/rank in the order I want them?

Thanks
Neil
From: Larry Linson on
Access does not have "entry order"; data in relational tables is, by
definition, unordered. You can sort on any field. The simplest approach for
"entry order" is to include a date-and-time field when entered, and to sort
on that.

For other non-alphabetic sorts, you could define a table which contains the
field value from the reference table and a numeric "sort order" field. Join
these in the Query, and sort on the "sort order" field.

I trust you are aware that the order in the table or query that is Record
Source of a Report is immaterial. The Report information is sorted
according to the Report's Sorting and Grouping properties.

Larry Linson
Microsoft Office Access MVP


"neil40(a)btinternet.com" <neil.grantham(a)googlemail.com> wrote in message
news:1b77e611-3668-40ea-aab9-2f7e6a595686(a)b33g2000yqc.googlegroups.com...
> In a League Table report I have the following layout
> Season Header - Group on 'Season' (EG 1948)
> League Header - Group on 'League' (A to Z)
> Detail - Sort by 'League Position'
>
> In the table, the League name comes from a separate table and on the
> Input form is available from a Drop-down control that is an after
> event of picking the season 'year'
>
> Leagues are ranked in the real world by importance (Senior teams,
> junior teams, trainee teams etc)
> I input data each year in that order
>
> My problem - I want to sort the Leagues such that the report outputs
> in the order I have added them to the table (is this not a wise
> strategy?) rather than the Alpha A-Z sort
> For example
> for a particular year, I might have
> Natoinal League 1
> National League 2
> Metropolitain League
>
> This is their order of seniority, but at present the Metropolitain
> League is printing as the first (as letter 'M' is ranking above 'N'
>
> What's the best strategy to remedy this/rank in the order I want them?
>
> Thanks
> Neil


From: neil40 on
On 28 Apr, 02:25, "Larry Linson" <boun...(a)localhost.not> wrote:
> Access does not have "entry order"; data in relational tables is, by
> definition, unordered. You can sort on any field.  The simplest approach for
> "entry order" is to include a date-and-time field when entered, and to sort
> on that.
>
> For other non-alphabetic sorts, you could define a table which contains the
> field value from the reference table and a numeric "sort order" field. Join
> these in the Query, and sort on the "sort order" field.
>
> I trust you are aware that the order in the table or query that is Record
> Source of a Report is immaterial.  The Report information is sorted
> according to the Report's Sorting and Grouping properties.
>
>  Larry Linson
>  Microsoft Office Access MVP
>
> "nei...(a)btinternet.com" <neil.grant...(a)googlemail.com> wrote in message
>
> news:1b77e611-3668-40ea-aab9-2f7e6a595686(a)b33g2000yqc.googlegroups.com...
>
>
>
> > In a League Table report I have the following layout
> > Season Header - Group on 'Season' (EG 1948)
> > League Header - Group on 'League' (A to Z)
> > Detail - Sort by 'League Position'
>
> > In the table, the League name comes from a separate table and on the
> > Input form is available from a Drop-down control that is an after
> > event of picking the season 'year'
>
> > Leagues are ranked in the real world by importance (Senior teams,
> > junior teams, trainee teams etc)
> > I input data each year in that order
>
> > My problem - I want to sort the Leagues such that the report outputs
> > in the order I have added them to the table (is this not a wise
> > strategy?) rather than the Alpha A-Z sort
> > For example
> > for a particular year, I might have
> > Natoinal League 1
> > National League 2
> > Metropolitain League
>
> > This is their order of seniority, but at present the Metropolitain
> > League is printing as the first (as letter 'M' is ranking above 'N'
>
> > What's the best strategy to remedy this/rank in the order I want them?
>
> > Thanks
> > Neil- Hide quoted text -
>
> - Show quoted text -

Thanks Larry,

I think I kind of knew that you couldn't sort that way.

Your suggestion of a date field seems good, and a quick search of this
group seems to suggest I could use Date() or Now()

However, do existing records have a 'hidden' date stamp?
I already have 500+ records in the table, and it would be desirable
that these existing records bear the timestamp they were entered?
If I add a field and use Date or Now, will it not give them all the
same time/date?

Going forward, the row would obviously pick up the date stamp of 'now'
as it is entered, which is fine, but to make this sort work I need to
use the date of when those 500 records were entered.
Is it possible?

Thanks again
Neil
From: Marshall Barton on
neil40(a)btinternet.com wrote:

>On 28 Apr, 02:25, "Larry Linson" <boun...(a)localhost.not> wrote:
>> Access does not have "entry order"; data in relational tables is, by
>> definition, unordered. You can sort on any field. �The simplest approach for
>> "entry order" is to include a date-and-time field when entered, and to sort
>> on that.
>>
>> For other non-alphabetic sorts, you could define a table which contains the
>> field value from the reference table and a numeric "sort order" field. Join
>> these in the Query, and sort on the "sort order" field.
>>
>> I trust you are aware that the order in the table or query that is Record
>> Source of a Report is immaterial. �The Report information is sorted
>> according to the Report's Sorting and Grouping properties.
>>
>>
>> "nei...(a)btinternet.com" wrote
>> > In a League Table report I have the following layout
>> > Season Header - Group on 'Season' (EG 1948)
>> > League Header - Group on 'League' (A to Z)
>> > Detail - Sort by 'League Position'
>>
>> > In the table, the League name comes from a separate table and on the
>> > Input form is available from a Drop-down control that is an after
>> > event of picking the season 'year'
>>
>> > Leagues are ranked in the real world by importance (Senior teams,
>> > junior teams, trainee teams etc)
>> > I input data each year in that order
>>
>> > My problem - I want to sort the Leagues such that the report outputs
>> > in the order I have added them to the table (is this not a wise
>> > strategy?) rather than the Alpha A-Z sort
>> > For example
>> > for a particular year, I might have
>> > Natoinal League 1
>> > National League 2
>> > Metropolitain League
>>
>> > This is their order of seniority, but at present the Metropolitain
>> > League is printing as the first (as letter 'M' is ranking above 'N'
>>
>> > What's the best strategy to remedy this/rank in the order I want them?
>>
>
>I think I kind of knew that you couldn't sort that way.
>
>Your suggestion of a date field seems good, and a quick search of this
>group seems to suggest I could use Date() or Now()
>
>However, do existing records have a 'hidden' date stamp?
>I already have 500+ records in the table, and it would be desirable
>that these existing records bear the timestamp they were entered?
>If I add a field and use Date or Now, will it not give them all the
>same time/date?
>
>Going forward, the row would obviously pick up the date stamp of 'now'
>as it is entered, which is fine, but to make this sort work I need to
>use the date of when those 500 records were entered.
>Is it possible?


The order the data was entered is not a attribute of the
entities your seasons table is modeling. IOW, using that as
the sorting mechanism is a bad idea. Instead, you should
use Larry's other idea about a little separate Leagues table
with the league id, league name/description and sorting
fields.

Your existing seasons table should only refer to the league
id field and not contain any orher information about the
league.

--
Marsh
MVP [MS Access]
From: neil40 on
On 28 Apr, 14:56, Marshall Barton <marshbar...(a)wowway.com> wrote:
> nei...(a)btinternet.com wrote:
> >On 28 Apr, 02:25, "Larry Linson" <boun...(a)localhost.not> wrote:
> >> Access does not have "entry order"; data in relational tables is, by
> >> definition, unordered. You can sort on any field.  The simplest approach for
> >> "entry order" is to include a date-and-time field when entered, and to sort
> >> on that.
>
> >> For other non-alphabetic sorts, you could define a table which contains the
> >> field value from the reference table and a numeric "sort order" field. Join
> >> these in the Query, and sort on the "sort order" field.
>
> >> I trust you are aware that the order in the table or query that is Record
> >> Source of a Report is immaterial.  The Report information is sorted
> >> according to the Report's Sorting and Grouping properties.
>
> >> "nei...(a)btinternet.com" wrote
> >> > In a League Table report I have the following layout
> >> > Season Header - Group on 'Season' (EG 1948)
> >> > League Header - Group on 'League' (A to Z)
> >> > Detail - Sort by 'League Position'
>
> >> > In the table, the League name comes from a separate table and on the
> >> > Input form is available from a Drop-down control that is an after
> >> > event of picking the season 'year'
>
> >> > Leagues are ranked in the real world by importance (Senior teams,
> >> > junior teams, trainee teams etc)
> >> > I input data each year in that order
>
> >> > My problem - I want to sort the Leagues such that the report outputs
> >> > in the order I have added them to the table (is this not a wise
> >> > strategy?) rather than the Alpha A-Z sort
> >> > For example
> >> > for a particular year, I might have
> >> > Natoinal League 1
> >> > National League 2
> >> > Metropolitain League
>
> >> > This is their order of seniority, but at present the Metropolitain
> >> > League is printing as the first (as letter 'M' is ranking above 'N'
>
> >> > What's the best strategy to remedy this/rank in the order I want them?
>
> >I think I kind of knew that you couldn't sort that way.
>
> >Your suggestion of a date field seems good, and a quick search of this
> >group seems to suggest I could use Date() or Now()
>
> >However, do existing records have a 'hidden' date stamp?
> >I already have 500+ records in the table, and it would be desirable
> >that these existing records bear the timestamp they were entered?
> >If I add a field and use Date or Now, will it not give them all the
> >same time/date?
>
> >Going forward, the row would obviously pick up the date stamp of 'now'
> >as it is entered, which is fine, but to make this sort work I need to
> >use the date of when those 500 records were entered.
> >Is it possible?
>
> The order the data was entered is not a attribute of the
> entities your seasons table is modeling.  IOW, using that as
> the sorting mechanism is a bad idea.  Instead, you should
> use Larry's other idea about a little separate Leagues table
> with the league id, league name/description and sorting
> fields.
>
> Your existing seasons table should only refer to the league
> id field and not contain any orher information about the
> league.
>
> --
> Marsh
> MVP [MS Access]- Hide quoted text -
>
> - Show quoted text -

OK, well here's how I structured it all, rightly or wrongly - would
value opinions
tblseason
Fields are ID/seasonfld/leaguefld/rank

Note - I added 'rank' in the last day or so with a thought at trying
to use that to order the leaugues as I want them.
seasonfld holds the year (1929/30/31 etc etc)
leaguefld holds the League names for these years
rank hold 1, 2, 3 etc

tblteams
Fields - ID/Teamname/nickname/etc etc

I have a Continuous form for input called MultiLgeInput and this is
updating another table called

tbltest
Fields are ID/Team/Season/League/played/won/drawn/lost/for/against/
position

The form uses some Drop down controls that
a) Allow selection of the Team name from tblteams
b) Allow selection of the season from tblseason with an after event to
c) Pick the League name from tblseason

My report is then based on tbltest and sorts and groups as stated in
the original post

The rank field I added doesn;t yet feature as I'm not sure if/how I
can integrate it at this stage or if I need to back track

Thanks
Neil