Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004

    Question Unanswered: Number of entries in one row

    It is possible to count number of entries in one row?

    Table: Players
    Fields: ID, Name,...Previous Clubs

    Previous Clubs

    Real, Bayer, Barcelona

    Sort Players by number of previous clubs

    Any solution?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Hi Bocky
    By defintion the number of entries in one row is one.

    Ok after that unhelpful answer I suspect you have a problem with your table design. The problem relates to how many slots do you allow as previous clubs if you store the previous clubs in the same row as the players name, do you make a guess and say 10, 20 or even more (you never know there may be a player out there who breaks such assumptions, or alternatively you find you run out of columns [ access only let you have 255 columns per table] anbd you could runt out of space).
    What happens if you find that you have mispelled the club's name, do you go through and edit each record with that club?
    How do you find any or all players who have played for that club? doing it you current way could be a nightmare to identify.

    I would suggest you need to redesign your tables at the atomic level or normalise the data. Looking at your example I suspect you are rolling up 2 different types of data into one table. As a general rule if you are referring to the same item of data in a sepcific row, or series of row in a table then the design is suspect. For example a player may have played for many clubs so that information should not be stored in the same table as a players details. A club may have many players, A league may have many clubs, a country may have many leagues. A continent may have many countries. In some circumstances it may be appropriate to define a column as an enumeration or limited list within the database, but I would honestly reccomend that you dont do that as there are always going to be problems. An example where it could be used is say gender (Male, Female or Unknown) or continent (eg Africa, Asia, Europe, North & South America) if you know at design time every possible value (but in the continent example you would have a problem if you decided to model as per FIFA and create new continent defintionsw such as Australasia & Central America

    So looking at the design:-
    I would suggest that you have a table to contain details of a Player, another table to store details of a players previous (& current) clubs, which in turn would suggest you should have another table to store details of clubs. Whilst you are at it may be you need another table to store details of the league (or country those clubs play in)

    In each table ask youself is there a clear ownership of the information involved, or a potential multiplication of data. Ie is the element you are trying to define unique to that area (ie there can be only one instance of the information at this level). It is arguable that the players current club could be defined at player level, however looking again a club has many players so you are storing the key to the club not the clubs name itself. If you want to look at a players history with clubs then it doesn't belong at the player level at all. You should define a sub table which cross references player and club (and store information such as date joned, date left). Once you have defined the table define a relationship between each table.

  3. #3
    Join Date
    Dec 2004
    Thanks for the answer. Although.....

    I have tables:

    League (id, name...)
    ClubDetails (id, name,.....)
    Player (id, name, id of current club, nationality.....Previous Clubs )
    etc ....
    So basically what i wanted to know is, if it is possible to count "words" seperated by comma.
    Example: ManU, Manchester City, Bolton . Number of "words"=5

    PS: I know that i should make new table to store information about player's previous clubs.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Not simply
    However you could write a function in VBA to return the number of clubs, assuming that each club was seperated by a comma, or a comma AND a space by using the string functions such as instr

    If you placed the function in a public module then the fnction would be available to any form or report or query.

    I would suggest you have a try at writing a function to do this and come back

    However had you stored details of the palyers previous clubs as a sub table then it woudl be a breeze with a bit of SQL. Hence my earlier diatribe.

    "ManU, Manchester City, Bolton" i count as 4 words not 5

    "Trafford Town, Manchester City & Bolton" now that comes to 5 words.

  5. #5
    Join Date
    Jul 2004
    Southampton, UK

    You should really consider Healdem's advice and create a new table for previous clubs. It will be worth the hour or so that it will take to do it. As well as being standard practice, it will save a lot of grief in the long run.

    Suppose you want to list all the players that have ever played for ManU. With seperate (normalised) tables, this is easy. In your current structure it's pretty difficult. It's difficult to predict the kind of things you'll want to do with the data in the future so by keeping the data in a normalised structure you give yourself the easiest route to solving most problems.


  6. #6
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    One more for the "please, PLEASE redesign your tables" crew.

    Good design allows for flexibility later. Like now for instance. Your entire solution could be a simple query:

    SELECT * FROM ClubHistory WHERE player_id = "whatever"

    That's it. The end.

    Now you have to write a custom VBA function to split a large string and return the number of values. Split functions are anoying, I wrote one as part of an array utility pack last week. You don't want to do that, trust me.

    What happens if you want to link those clubs together by different criteria later? Maybe you want to start tracking what dates they started with each club? Maybe you want to know which players were in the same clubs at the same time?

    You can't perceive what data you may want tomorrow, it is therefore advisable to provide maximum flexibility.

    PS: Looking at your proposed structure a bit further, I would also recommend additional tables for your game details. I'm guessing that's supposed to track details for more then one game.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts