Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Unanswered: "If, A then count x, y, & z..." possible in ACCESS query?

    I have a table of attendances to 5 different groups for 300+ clients. I want to count the attendance for each group for each client. So if a client has gone to Group Red, then I want to count how many times that client went to that Group Red. In the end, I want to know how many times each client went to each of the five groups. Is this possible to do in a query (in ACCESS 2003)? If not, how else might I be able to find out this information?

    Thanks,
    Nolla

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select clientname, groupname, count(*)
    from yourtable
    group by clientname, groupname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Posts
    11
    Can I count a range of fields?

    thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure, definitely, probably, if you would explain what that means
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    11
    Like in an excel spreadsheet "A1:A22" would count all items from A1 through A22 even if you have fields that go up to "A40"

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Rudy does not appear to be online, but I agree with "sure, definitely, probably". It sounds like you would want to add a WHERE clause to the SQL that would restrict the records counted as appropriate to your needs. To continue with your example, what is it about the records in A1-A22 that make you want to include them while excluding the rest?
    Paul

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the GROUP BY and the WHERE sub clause of SQL

    what identifies A:1..A:22?
    is that a specific group or block of data

    ie are you effectively asking the db
    count then number of times person X has attended classess
    OR
    count then number of persons attending class X

    if so then WHERE is probably your best mate

    count then number of times each person has attended classess

    then GROUP BY couldbe your best mate...

    this assumes that you have designed you data storage using the relational rather than a spreadsheet style.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2006
    Posts
    73
    Something similar to this since I felt like not reposting a new topic on it..

    I'm trying to get a count of how many times a customer's rank appears in a table in a report..would I be safe to say that GROUP BY would be my best bet? or is there a better way to obtain the number?


    For example:

    Cust 1 has a ranking of B
    Cust 2 has a ranking of A
    Cust 3 has a ranking of B

    So in the report it should show the totals of Customers with a ranking of A(1) and B(2)

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, a GROUP BY would be the best way to summarize that info.
    Paul

Posting Permissions

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