Results 1 to 10 of 10
  1. #1
    harapan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4

    How Counting Active Days?

    Dear experts,

    Let me explain my condition. I have a table as below:

    Code:
    NAME DATE
    JOE 1-Jan-12
    TED 1-Jan-12
    MARK 2-Jan-12
    JOE 3-Jan-12
    JOE 3-Jan-12
    MARK 3-Jan-12
    and I would like to count how many days is these men active. And the query result should be like this:



    Code:
    NAME DAYS_ACTIVE
    JOE 2
    TED 1
    MARK 2
    Your help is very appreciated. Thank you.

    Sincerely,
    Harapan
    Last edited by harapan; 04-02-2012 at 01:24 AM. Reason: Removing Name "Bob"

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just check out if below gives some guidelines :
    Code:
    SELECT 
        qryDistinctEmpNameActiveDates.empName, 
        Count(qryDistinctEmpNameActiveDates.activeDate) AS CountOfactiveDate
    FROM 
    (
    SELECT DISTINCT 
        tblEmpActiveDates.empName, 
        tblEmpActiveDates.activeDate
    FROM 
        tblEmpActiveDates
    )
    AS qryDistinctEmpNameActiveDates
    GROUP BY 
        qryDistinctEmpNameActiveDates.empName;
    Thanks

  3. #3
    jhargram is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    40
    Hi Harapan, you can try this as Query,

    select distinct name, count(name) as days_active from table group by name;

    I think it will solve problem...

  4. #4
    jhargram is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    40
    Hi Harapan, you can try this as Query,

    select distinct name, count(name) as days_active from test group by name;

    I think it will solve problem...

  5. #5
    harapan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4
    Halo recyan,
    Could you please rework it to accomodate my variable names? tx.

    Halo jhargram,
    So there is no dependency on DATE column?

  6. #6
    jhargram is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    40
    I don't think this is required in this case.
    Else, you can use some different way like:
    count date where name='xx' but for that you need to design your databse.


    but in this case " I think that query will work properly if name is not null. and also data is there.

  7. #7
    harapan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4
    jhargram,

    I see dim light here. how could I count(DATE) distinctly?

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by recyan View Post
    Just check out if below gives some guidelines :
    Code:
    SELECT 
        qryDistinctEmpNameActiveDates.empName, 
        Count(qryDistinctEmpNameActiveDates.activeDate) AS CountOfactiveDate
    FROM 
    (
    SELECT DISTINCT 
        tblEmpActiveDates.empName, 
        tblEmpActiveDates.activeDate
    FROM 
        tblEmpActiveDates
    )
    AS qryDistinctEmpNameActiveDates
    GROUP BY 
        qryDistinctEmpNameActiveDates.empName;
    Thanks
    Quote Originally Posted by harapan View Post
    Halo recyan,
    Could you please rework it to accomodate my variable names? tx.
    Replace tblEmpActiveDates with your table name; empName with the name of your Name field; activeDate with the name of your Date field in the query & try running it.


    Thanks

  9. #9
    harapan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    4
    recyan,

    it works. you're my man! thanks

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Glad you found it helpful.

    Thanks

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

Similar Threads

  1. counting days
    By d3pl0y3d in forum Access
    Replies: 13
    Last Post: 04-28-2011, 06:31 PM
  2. Select Dates between days and two days ago?
    By rstonehouse in forum Queries
    Replies: 4
    Last Post: 08-18-2010, 02:13 AM
  3. Counting Active Records Between 2 Dates
    By catat in forum Queries
    Replies: 5
    Last Post: 07-28-2010, 10:55 AM
  4. Inactive or active
    By Bruzer in forum Access
    Replies: 4
    Last Post: 04-08-2010, 04:20 PM
  5. Replies: 4
    Last Post: 08-27-2009, 01:21 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