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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 | |
|
|