Prev: how do I not show a record in a report when the total is zero?
Next: exporting a report in text - comma delimited format
From: neil40 on 4 May 2010 02:54
On 3 May, 17:28, Marshall Barton <marshbar...(a)wowway.com> wrote:
> nei...(a)btinternet.com wrote:
> >On 1 May, 17:26, Marshall Barton wrote:
> >> nei...(a)btinternet.com 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
First  |  Prev  | 
Pages: 1 2 3
Prev: how do I not show a record in a report when the total is zero?
Next: exporting a report in text - comma delimited format