Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: need help counting

    Hello All,
    I need help on a report. The user want a report to count if a the field contains "y". Also, They want to count the fields with multiple "y". For example:

    Name Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Green Y Y Y White Y Y Y
    Gray Y Y Y
    Brown Y Y Y
    Black Y Y Y
    Say their are five names on the report. Their are seven fields.
    The first name has "Y" for TXT_Field1, TXT_Field7 and TXT_Field8. The rest of the fields are blank.
    The second name has "Y" for TXT_Field1, TXT_Field7 and TXT_Field8. ""
    The third name has "Y" for TXT_Field2, TXT_Field7 and TXT_Field8. ""
    The fourth name has "y" for TXT_Field2, TXT_Field7 and TXT_Field8. ""
    The fifth name has "y" for TXT_Field1, TXT_Field7 and TXT_Field8. ""
    So for the total on the page:
    Fields 1, 7 and 8=3
    Fields 2, 7and 8 =2 Total =5

    They want the total number of people on the report but broken down to what fields each has.

    I've been trying to use

    Code:
    Code:
    =Sum(IIf([TXT_Field1] ="y" And [TXT_Field7] ="y" And [TXT_Field8]="y", 1, 0))
    I keep getting the total number of records in the database (250) instead of the breakdown for each field. For example: Field1=250 Field2=250 etc...
    I'm looking for something like:
    Names with field1 = "Y" is 50
    Names with field1 and field2 = "Y" is 80
    Names with field1 and field2 and field3 ="Y" 10
    Names with field1 and field2 and field3 and field4="Y" 70
    Names with field1 and field2 and field3 and field4 and field5="Y" 10
    Names with field1 and field2 and field3 and field4 and field5 and field6="Y" 10
    Names with field1 and field2 and field3 and field4 and field5 and field6 and field7="Y" 10
    Names with field1 and field2 and field3 and field4 and field5 and field6 and field7 and field8="Y" 10
    I am hoping I'm explaining this clear enough for you to help me. It took me a long time to realize what they wanted.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please use [code]...[/code] tags

    like this --
    Code:
    Name  Field1  Field2  Field3  Field4  Field5  Field6  Field7  Field8
    Green     Y                                               Y       Y
    White     Y                                               Y       Y
    Gray              Y                                       Y       Y
    Brown             Y                                       Y       Y
    Black     Y                                               Y       Y
    now please show the actual results you want from the query
    Last edited by r937; 11-07-07 at 12:52.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thank you. I didn't know I could use them for formatting also.

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    Code:
    Name  Field1  Field2  Field3  Field4  Field5  Field6  Field7  Field8
    Green     Y                                               Y       Y
    White     Y                                               Y       Y
    Gray              Y                                       Y       Y
    Brown             Y                                       Y       Y
    Black     Y                                               Y       Y
    Given the above:
    Number of people with Field1, Field7 and Field8="Y's" is 3
    Number of people with Field2, Field7 and Field8="Y'" is 2
    Total is 5

    They want the total number of people on the report but broken down to what fields each has.

    I've been trying to use

    Code:
    Code:
    =Sum(IIf([TXT_Field1] ="y" And [TXT_Field7] ="y" And [TXT_Field8]="y", 1, 0))
    This code gives me 5 when it should give me 3
    Code:
    Name  Field1  Field2  Field3  Field4  Field5  Field6  Field7  Field8 
    Green     Y                                               Y       Y                         
    White     Y                                               Y       Y
    Gray              Y                                       Y       Y
    Brown             Y                                       Y       Y
    Black     Y                                               Y       Y
    Results
    Green 1
    White 1
    Black  1
    Total: 3
    Code:
    =Sum(IIf([TXT_Field2] ="y" And [TXT_Field7] ="y" And [TXT_Field8]="y", 1, 0))
    This gives me 5 when it should give me 2
    Code:
    Name  Field1  Field2  Field3  Field4  Field5  Field6  Field7  Field8 
    Green     Y                                               Y       Y                         
    White     Y                                               Y       Y
    Gray              Y                                       Y       Y
    Brown             Y                                       Y       Y
    Black     Y                                               Y       Y
    Results
    Gray 1
    Brown 1
    Total: 2
    It seems to be counting the number of people in the report.
    The results should give me a total number of people with fields = "Y" that I ask for.

    If I ask for the number of people that have "Y" in Field1, Field7 and Field8; the total should be 3

    If I ask for the number of people that have "Y" in Field2, Field7 and Field8; the total should be 2

    If I ask for the number of people that have "Y" in Field1,Field2, Field7 and Field8; the total should be 0.
    etc...

    I hope I am clear enough for you to help me
    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slimjen
    They want the total number of people on the report but broken down to what fields each has.
    you realize that there are hundreds of combinations, yes?

    please give the expected output from the query for the sample data you provided
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2004
    Posts
    214
    Yes; I do realize that there are many possible combinations. If I get a code that works, I can go through the possible scenerios. I thought I'd given the expected output from the total expected in post #4. Total being 5.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slimjen
    I thought I'd given the expected output from the total expected in post #4. Total being 5.
    ah, i see

    okay, here ya go:

    Code:
    select sum(rows) as total
      from (
           select count(*) as rows
             from daTable
           group
               by Field1
                , Field2
                , Field3
                , Field4
                , Field5
                , Field6
                , Field7
                , Field8 
           ) as d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2007
    Posts
    15
    why don't you use counta since your counting text data right??? y isn't a number.

    also you have to tell it to count by the specific data in field called "name"

    =Counta(IIf([TXT_Field1] ="y" And [TXT_Field7] ="y" And [TXT_Field8]="y" And [Name]="green", 1, 0))

    but this should give you 3 greens which I don't think is what you want considering you want 1 green 1 white and 1 black.

    somehow should be able to tell it to count by field1 by field name [Txt_Field1],[Name]

    I am sure that isn't the right syntax as I am just trying to example a concept.

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    I am totally confused from your post #7. How and where do I put this sql statement into an Access Report Footer.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ummm...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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