Results 1 to 3 of 3
  1. #1
    mircoaccess87 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    4

    Question Counting the distinct values of a column group by another column

    Dear All,

    Here is my problem:

    I have this table ("people") and an example of possible rows:
    id(key) COL 1 COL2 department country name
    1 xx yy KPP USA John
    2 zz kk KPP USA John
    3 ss ff TLL USA John
    4 ww qq PPO Italy Marco
    5 jj uu PPO Italy Marco

    I have to count the number of distinct DEPARTMENT for each NAME;
    so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).



    I have tryed in this way:

    SELECT
    COUNT(DISTINCT department) AS NumberOfDifferentDepartments
    FROM people
    GROUP BY name;

    But Access says me there is a syntax error.

    Has someone a possible solution?

    Thank you very much.

    I'm working with MS Access 2002.

    Mirco.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can use two queries - the first one groups on department and name (reading from the table), and the second one groups by name and counts by department (reading from the first query). It may be possible to be done in one but I don't know how!

  3. #3
    mircoaccess87 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    4

    Thumbs up

    It works.

    Thank you very much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-28-2011, 04:29 PM
  2. Distinct for one Column
    By crispy-bacon in forum Queries
    Replies: 2
    Last Post: 05-31-2011, 07:21 AM
  3. help counting distinct values
    By joo-joo-man in forum Queries
    Replies: 2
    Last Post: 10-17-2010, 05:18 AM
  4. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums