A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Non alphabetic sorting



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 01:15 AM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 60
Default Non alphabetic sorting

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
  #2  
Old April 28th, 2010, 02:25 AM posted to microsoft.public.access.reports
Larry Linson
external usenet poster
 
Posts: 3,112
Default Non alphabetic sorting

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


" wrote in message
...
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



  #3  
Old April 28th, 2010, 10:49 AM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 60
Default Non alphabetic sorting

On 28 Apr, 02:25, "Larry Linson" 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

" wrote in message

...



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
  #4  
Old April 28th, 2010, 02:56 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Non alphabetic sorting

wrote:

On 28 Apr, 02:25, "Larry Linson" 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.


" 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]
  #5  
Old April 28th, 2010, 04:04 PM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 60
Default Non alphabetic sorting

On 28 Apr, 14:56, Marshall Barton wrote:
wrote:
On 28 Apr, 02:25, "Larry Linson" 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.


" 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
  #6  
Old April 29th, 2010, 03:51 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Non alphabetic sorting

wrote:

On 28 Apr, 14:56, Marshall Barton wrote:
wrote:
On 28 Apr, 02:25, "Larry Linson" 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.


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


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



Sorry, but I can't figure out what your tables are modeling.
I thought your original question was about sorting the
leagues, but you don't appear to have a leagues table with
information about each league. Your idea of the rank field
in the seasons table implies that you intend to sort the
leagues differently each season.

It's possible, but potentially difficult to manage, to use
league name in the seasons table and kind of implies that
the leagues and their names change every season. If not,
you will have a lot of problems should you ever need to
change a league name (eg. fix spelling, etc). As I said
before, the league name should only appear once in a leagues
table instead of many times in the seasons table. The
leagues table should be exclusivly used to hold league data,
including the sorting information needed to answer your
original question.

If you are having trouble getting your head around how to
design you tables to meet all your needs, I suggest that you
make a list of your objectives, including such things as
sorting the leagues. Then figure out what data you have or
need to add to meet those objectives and only then design
the appropriate tables to deal with all that. If you need
help with organizing the data tables, I suggest that the
best place to get it is in the tables design forum.

Once all that is properly set up, then you can start fixing
up your forms and reports. Right now, fooling around with
forms and reports is mostly wasted effort (except possibly
to highlight the need for additional data such as something
to assist sorting.)

--
Marsh
MVP [MS Access]
  #7  
Old April 30th, 2010, 02:40 PM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 60
Default Non alphabetic sorting

On 29 Apr, 15:51, Marshall Barton wrote:
wrote:
On 28 Apr, 14:56, Marshall Barton *wrote:
wrote:
On 28 Apr, 02:25, "Larry Linson" 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.


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


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


Sorry, but I can't figure out what your tables are modeling.
I thought your original question was about sorting the
leagues, but you don't appear to have a leagues table with
information about each league. *Your idea of the rank field
in the seasons table implies that you intend to sort the
leagues differently each season.

It's possible, but potentially difficult to manage, to use
league name in the seasons table and kind of implies that
the leagues and their names change every season. * If not,
you will have a lot of problems should you ever need to
change a league name (eg. fix spelling, etc). *As I said
before, the league name should only appear once in a leagues
table instead of many times in the seasons table. *The
leagues table should be exclusivly used to hold league data,
including the sorting information needed to answer your
original question.

If you are having trouble getting your head around how to
design you tables to meet all your needs, I suggest that you
make a list of your objectives, including such things as
sorting the leagues. *Then figure out what data you have or
need to add to meet those objectives and only then design
the appropriate tables to deal with all that. *If you need
help with organizing the data tables, I suggest that the
best place to get it is in the tables design forum.

Once all that is properly set up, then you can start fixing
up your forms and reports. *Right now, fooling around with
forms and reports is mostly wasted effort (except possibly
to highlight the need for additional data such as something
to assist sorting.)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -


Think I used 'Reply to author' Marsh
Hope you got it. Would you rather I had replied to the list

Neil
  #8  
Old May 1st, 2010, 05:26 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Non alphabetic sorting

wrote:
Think I used 'Reply to author' Marsh
Hope you got it. Would you rather I had replied to the list


Yes. I finally found it and am adding it here.

Perhaps I can explain a bit about what I am doing.

The database is to record a history of Motor Cycle Speedway here in
the UK - for the uninitiated, it's 4 men competing over 4 laps (2 per
team) and 13 races that produces a result (Win lose or draw etc)
Teams have come and gone, some still survive. Unlike some sports where
League names are consistent (for example the 'Conferences' in NBA etc)
so each team compete over the years in a variety of different League
names.
I am not recording the individual 'meet' result, but the end of season
table
The tblseason 'table' has the names of these Leagues
So for example in 1928 there were 2 Leagues, recorded thus in the
Access Table
seasonfld leaguefld Rank
1928 Northern League 1
1928 Southern League 2
1929 Northern League 1
1929 Southern League 2
.
.
1956 National League 1 1
1956 National League 2 2
1956 Metro League 3
etc
etc

Quite complicated in structure. Early years the Leagues were 'equal'
and teams competed in areas much like NBA conferences
Later on, there are 'junior' Leagues
For example as above, 1956
NL 1 was the senior League, NL 2 a more 'Amateur' League and Metro the
'baby' league.
This has continued by and large to this day with similar structure but
changing League names

Thus, with what I have posted earlier, if I group and sort the Metro
League comes on my report above NL 1 and 2. Not what I want (hence my
attempt at adding 'Rank')

The other 'team' table records the Teams venues, Trophies, Nicknames
etc.

The 'test' table was my attempt to record the end of season data, and
the report I am seeking help with here, is to print/display annual
Leagues (year per page)

Thanks for your time, I'd be interested to know if you still belive
structure is wrong.


With the league names being semi random, there's not much
use for a Leagues table. In that case, I guess adding the
rank field to the seasons table is ok. I don't like the
idea of having to add a rank number for every record, but I
don't readily see an easy way around it either.

Seems like with your Rank field, all you have to do is sort
the report on both the season and rank fields. That makes
the report trivally easy and maybe that's some kind of
compensation for entering all those rank numbers.

--
Marsh
MVP [MS Access]
  #9  
Old May 1st, 2010, 11:00 PM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 60
Default Non alphabetic sorting

On 1 May, 17:26, Marshall Barton wrote:
wrote:
Think I used 'Reply to author' Marsh
Hope you got it. Would you rather I had replied to the list


Yes. *I finally found it and am adding it here.





Perhaps I can explain a bit about what I am doing.


The database is to record a history of Motor Cycle Speedway here in
the UK - for the uninitiated, it's 4 men competing over 4 laps (2 per
team) and 13 races that produces a result (Win lose or draw etc)
Teams have come and gone, some still survive. Unlike some sports where
League names are consistent (for example the 'Conferences' in NBA etc)
so each team compete over the years in a variety of different League
names.
I am not recording the individual 'meet' result, but the end of season
table
The tblseason 'table' has the names of these Leagues
So for example in 1928 there were 2 Leagues, recorded thus in the
Access Table
seasonfld *leaguefld * * * * * * * Rank
1928 * * * * Northern League * *1
1928 * * * * Southern League * *2
1929 * * * * Northern League * *1
1929 * * * * Southern League * *2
.
.
1956 * * * * National League 1 * 1
1956 * * * * National League 2 * 2
1956 * * * * Metro League * * * * 3
etc
etc


Quite complicated in structure. Early years the Leagues were 'equal'
and teams competed in areas much like NBA conferences
Later on, there are 'junior' Leagues
For example as above, 1956
NL 1 was the senior League, NL 2 a more 'Amateur' League and Metro the
'baby' league.
This has continued by and large to this day with similar structure but
changing League names


Thus, with what I have posted earlier, if I group and sort the Metro
League comes on my report above NL 1 and 2. Not what I want (hence my
attempt at adding 'Rank')


The other 'team' table records the Teams venues, Trophies, Nicknames
etc.


The 'test' table was my attempt to record the end of season data, and
the report I am seeking help with here, is to print/display annual
Leagues (year per page)


Thanks for your time, I'd be interested to know if you still belive
structure is wrong.


With the league names being semi random, there's not much
use for a Leagues table. *In that case, I guess adding the
rank field to the seasons table is ok. *I don't like the
idea of having to add a rank number for every record, but I
don't readily see an easy way around it either.

Seems like with your Rank field, all you have to do is sort
the report on both the season and rank fields. *That makes
the report trivally easy and maybe that's some kind of
compensation for entering all those rank numbers.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -


Thanks Marsh

As the rank field only currently exists, as an afterthought to try and
make this work, in my tblseasons, what is the best way to get it to my
existing report.

Somehow get it added to the tbltest that is updated as stated via
links to other tables and drop downs with after events? Maybe I could
add the rank field so that it fills in automatically when the League
is selected from tblseasons?
Via a query? Re-do the report?

Your advice, as always is most welcomed

Neil
  #10  
Old May 3rd, 2010, 05:28 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Non alphabetic sorting

wrote:

On 1 May, 17:26, Marshall Barton wrote:
wrote:
The database is to record a history of Motor Cycle Speedway here in
the UK - for the uninitiated, it's 4 men competing over 4 laps (2 per
team) and 13 races that produces a result (Win lose or draw etc)
Teams have come and gone, some still survive. Unlike some sports where
League names are consistent (for example the 'Conferences' in NBA etc)
so each team compete over the years in a variety of different League
names.
I am not recording the individual 'meet' result, but the end of season
table
The tblseason 'table' has the names of these Leagues
So for example in 1928 there were 2 Leagues, recorded thus in the
Access Table
seasonfld *leaguefld * * * * * * * Rank
1928 * * * * Northern League * *1
1928 * * * * Southern League * *2
1929 * * * * Northern League * *1
1929 * * * * Southern League * *2
.
.
1956 * * * * National League 1 * 1
1956 * * * * National League 2 * 2
1956 * * * * Metro League * * * * 3
etc
etc


Quite complicated in structure. Early years the Leagues were 'equal'
and teams competed in areas much like NBA conferences
Later on, there are 'junior' Leagues
For example as above, 1956
NL 1 was the senior League, NL 2 a more 'Amateur' League and Metro the
'baby' league.
This has continued by and large to this day with similar structure but
changing League names


Thus, with what I have posted earlier, if I group and sort the Metro
League comes on my report above NL 1 and 2. Not what I want (hence my
attempt at adding 'Rank')


The other 'team' table records the Teams venues, Trophies, Nicknames
etc.


The 'test' table was my attempt to record the end of season data, and
the report I am seeking help with here, is to print/display annual
Leagues (year per page)


With the league names being semi random, there's not much
use for a Leagues table. *In that case, I guess adding the
rank field to the seasons table is ok. *I don't like the
idea of having to add a rank number for every record, but I
don't readily see an easy way around it either.

Seems like with your Rank field, all you have to do is sort
the report on both the season and rank fields. *That makes
the report trivally easy and maybe that's some kind of
compensation for entering all those rank numbers.


As the rank field only currently exists, as an afterthought to try and
make this work, in my tblseasons, what is the best way to get it to my
existing report.

Somehow get it added to the tbltest that is updated as stated via
links to other tables and drop downs with after events? Maybe I could
add the rank field so that it fills in automatically when the League
is selected from tblseasons?
Via a query? Re-do the report?



The report's record source query would have to be modified
to include the rank field and the report would have to be
modified to add the rank field to its sorting list. Both
very simple changes.

Adding the rank values to existing records in the table will
require a fair amount of manual work, if for no other reason
than to verify any automatic tricks you try to use. I think
I might start by using a series of Update queries similar
to:
UPDATE tblseasons
SET rank = 3
WHERE leaguename = "baby league"

After a half dozen or so of those, the seasons table should
be mostly fixed and the remaining changes can be done
manually without too much work.

In the future, your data entry form clearly would need a way
to enter the rank value via eiher a text or combo box or
whatever. Don't forget to add the rank field to the form's
record source query so you can the text/combo box bound.

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.