Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2009
    Posts
    14

    Question Unanswered: How to count a vtext value occurance?

    Hi,

    This is my first post here.

    Version: Access 2003.
    Problem:

    I have a DB that has a table called tbl_Team. The column (fields) heading are P1, P2, P3 ..... P20.

    I have names in the fields like David, Paul etc.

    P1 P2 P3 P4
    David Paul Mark Kevin
    Fred David Paul Kevin

    I needed to be able to count the number of times a name appears. These results need to be in a seperate table next to the persons name.

    eg.
    David | 27
    Paul | 30

    Once I have the People table populated with the names and the result I can use it to do other things.

    In Excel you can use Countif function. But there is nothing like that in Access and I am totally stumped.

    Can anyone help please?

    Lensmeister.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Welcome to the forum.
    I see that you have created "Excel databases" in the past. One thing to learn very quickly, it will save you enormous problems, is Spreadsheets and Relational Databases are nothing like the same and should not be treated as such.

    Relational Databases are grounded in mathematical theory. I don't want to cover it here. This link provides the very minimum you should know before attempting to design a database. The very short answer is: that might be a good way of "storing" data in a spreadsheet, it is definitely not the way to store relational data.
    The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You could write some code to add each field to a table then run a query to do your totals. Alternatively, create 20 append queries to add the 20 fields ( 1 query for each field) to a table then a new query to do your totals.

  4. #4
    Join Date
    May 2009
    Posts
    14
    Hi guys,

    Thanks.

    I am wondering if maybe with the table in access, I might be better to perform a count using VB6. And when the VB6 form is loaded then have the code 'execute' and provide the value.

    This is something I am going to have to think about.

    Any other suggestions welcomed.

    THe "counting" of the number of times a name appears in a table is one of the main stumling blocks that's holding me back.

    Thanks again all,

    Lensmeister

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Better to design the table properly.Then it is *trivial*
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2009
    Posts
    14
    The table is "fine",

    Field titles are:

    ID - (This is an autonumber & Primary key)
    P1 - (Text) thru to P20.

    These are linked to other tables by the ID number via relationship.

    I did Access courses (fundamentals & Intermediate) through work about a year ago but the tutors could not give me an answer on this, but having had advice on other forums regarding Excel I hoped someone here could help me.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your tutors were wrong.
    Read the link I posted.

    If you can explain why this is not a 1NF violation then fine, but at least address this point.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2009
    Posts
    14
    Ok I will do my best to explain this.

    The people table (tbl_People) will need to calculate the number times the name appears in another table (tbl_Totals).

    I will try to explain myself in how a current version works in excel.

    On sheet 'total' is the formula =countif(array (on sheet people ,cell (eg A1 on sheet 'total')

    This then counts all the times David is listed in array B1:G10.

    On the link you posted it says 1NF "A table is in first normal form if all the key attributes have been defined and it contains no repeating groups."

    Does this mean I cannot count the whole Table?

    Sorry but I am finding the NF1/2/3 thing confusing.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yup - the table is not in First Normal Form.

    It is the "repeating groups" that is the problem. As soon as you have columns enumerated by name, that is a warning sign. As soon as you are limited to N items from a list due to this (to have 21 names, you need to change the table), that is a warning sign. As soon as you have N columns that all store the same type of data (the term is "the same domain"), that is a warning sign. This list should be vertical not horizontal.

    Ignore 2 and 3 for now. Do you see what the design would be to make it compatible with 1NF?
    Last edited by pootle flump; 05-15-09 at 10:53.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2009
    Posts
    14
    Ok, as it is a football team, each line is a team lineup and links to the matches table via the ID primary key.

    if the table had teams vertical they would not link correctly. Am I right there?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope

    I will be back - just offline for a mo....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2009
    Posts
    14
    ok ... I have a sample mdb if you want to have a look.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Go on then - that would work.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    May 2009
    Posts
    14
    There are only about 400 matches listed on here and no names.

    The full DB will have over 2000 names and over 3500 matches.
    Attached Files Attached Files

  15. #15
    Join Date
    May 2009
    Posts
    14
    Did anyone get to have a look at the sample ?

    Thanks
    Access 2002 :: Windows XP (Home) :: Visual Basic 6 :: Excel 2003 ::

Posting Permissions

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