Results 1 to 4 of 4

Thread: count 6

  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: count 6

    i have a table it has 41 fields some for them are numbered 1 to 36 in each of these fields there is a number from 1 to 6 i want to count the number of 6s in all the fields for each record. in a form or query.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It sounds like you have repeating groups:
    Fundamentals of Relational Database Design -- r937.com
    This is typically considered a design problem and violation of first normal form. If so, then the proper design is in that link and the solution is trivial.
    Otherwise, you will have write a very, very long Iif() expression.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2004
    Posts
    178
    this table is for shooting comp each of the fields has there score for each round i have attched the table
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yup, then it is a repeating groups problem. This is a one-to-many relationship and there should be two tables, with the round number as values in a single column, not 41 columns in one table. Refer to the link for more information about proper database design.

    I would redesign your table. If you are not prepared to do this then learn to love Ctrl + C and Ctrl + V.
    Code:
    SELECT SUM(iif(col1 = 6, 1, 0) + iif(col2 = 6, 1, 0) +......) AS SumOfMyDenormalsedDatabase
    FROM myTable
    Note that not spending time getting your design right in the first place will mean you will have to write more and more hacky code like this to fulfil the simplest of requirements.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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