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
  #11  
Old May 4th, 2010, 07:54 AM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 60
Default Non alphabetic sorting

On 3 May, 17:28, Marshall Barton wrote:
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]- Hide quoted text -

- Show quoted text -


Marsh
All now working as planned.
I was able to add a Rank to my target table and fill in missing values
relatiively easily.
I now have an after event in my form that auto fills this when I
select the League from a drop down, changed the sorting to the Rank
field in my report and that's now giving the ouptut I need

Thanks for sticking with this
Much appreciated,
Neil
 




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 12:22 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.