Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distinct Count in Query (Access 97, Win 95/98)

    Hi--

    I'm trying to write a Distinct Count type query, using only one query, in Access 97. I'm beginning to think this is not possible. Has anyone been able to do it? If so, can you post a sample?

    Here's what I've got:

    Employee1 Sunday
    Employee2 Sunday
    Employee1 Monday
    Employee2 Tuesday

    I'd like to be able to run something like this:
    Select Count(Distinct EmpNumber) from tblWorkdays

    But all I get is a syntax error. Apparently the guys who wrote Access97 Developer's Handbook could do it, because the results of such a query are an example on page 140, but the query is not there.

    I've even tried it with a subquery, but the query doesn't run (and it doesn't even give me an error message! Imagine that!).

    TIA!

    Cecilia :-)

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    I'm not sure what output you're looking for. If you want the number of employees working on each day, then try an aggregate query without using the Distinct keyword. For example, try something like:
    <font face="Georgia">
    SELECT Workday, Count(EmpNumber) AS NumberOfEmp
    FROM tblWorkdays
    GROUP BY Workday;
    </font face=georgia>
    Using your example data, this produces the following output:
    <font face="Georgia">
    Workday NumberOfEmp
    Monday 1
    Sunday 2
    Tuesday 1
    </font face=georgia>
    Is this what you're looking for? (With a little embellishment, you can get the days of the week to list in order, too.)

    Hope this helps.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    Almost. I'm looking for

    Count(Distinct EmpNumber)

    Which would return 2 as the result. But it doesn't seem to work in Access 97, so I'm looking for an alternative and/or confirmation that it just doesn't work.

    If you do Count(EmpNumber), the answer is 4 since there are four records. I know you can do the above with two queries (first is a group by, then a count), but I'm trying to do it with just one query.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    Hi Cecilia,

    I am a little confused (as always...) Anyway, is Employee 1 Sunday one record or two in the table or is the table set up as employee 1 and the days the employee worked?? This will make a difference in the answer.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    I think this is what you are after.

    Create a table with the days of the week listed.

    My Table3. One Field. [Weekdy]

    Monday
    Tuesday
    Wednesday
    Thursday
    Friday
    Saturday
    Sunday

    Table 2 has the fields [workdy] and [Employee]

    [workdy] [Employee]
    Monday employee1
    Sunday employee2
    Sunday employee1
    Tuesday employee1

    Here is the SQL:

    SELECT Count(Table2.workdy) AS CountOfworkdy, Table2.workdy
    FROM Table3 INNER JOIN Table2 ON Table3.weekdy = Table2.workdy
    GROUP BY Table2.workdy;

    Results:

    [CountofWorkdy] [Workdy]

    1 Monday
    2 Sunday
    1 Tuesday

    HTH

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    Hi Gary....

    The table (actually, it's a query of a larger table) contains a list of employees with the days of the week (in reality, it just picks up employees that worked on a Sat, Sun, or a holiday) and the hours they worked that day. I need to count how many people worked on those days. The trouble is that there is a potential that someone could work Saturday and Sunday and a holiday, so their name shows up three times in the query. When you do a count, it picks up the number of records, not the number of unique individuals.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    Okay, it's Friday. I'm going home.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query -- Clarification

    Hi--

    Thanks for the try, but it's not what I'm after. I have a table that shows who did what on what day. From there, I have a query that shows employees that worked on a weekend or holiday and the hours they reported working.

    So my query ends up with

    Emp1 Sunday
    Emp1 Saturday
    Emp2 Sunday
    Emp3 Holiday
    Emp3 Sunday

    I want to return that there are three individuals reporting that they worked on a weekend or holiday. This is a bit closer to the real life query, so maybe it clarifies what I'm trying to do.

    I _could_ get my result by making a group by query for EmployeeID, then counting the number of rows in the query. But I don't want to use two queries because I'm programmatically changing everything step by step (I know I could change the querydefs, but I don't want to do that either).

    So what I want is the Access97 equivalent of Count(Distinct EmployeeID), which so far has not worked for me, despite the fact that it shows up on page 140 of the Access Developer's Handbook, and I haven't been able to find proof that it doesn't work or a workaround for the issue.

    If anyone has any other ideas....

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    I understand your question now (I think). For Access 97, I'm stumped, too (so far). Access 2000 permits a subquery in the FROM clause; for example:
    <font face="Georgia">
    SELECT Count(EmpNumber) as CountEmp
    FROM (SELECT DISTINCT EmpNumber FROM tblWorkdays);
    </font face=georgia>
    which generates a result of 2 (for your example data), but this syntax apparently is not allowed in Access 97....

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    Sure wish I could. It's Friday and a beautiful day outside.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    That's what I'm afraid of. I can get subqueries to work with Group By and Sum, but I haven't been able to get it to work with Count, which of course is what I really need at the moment....

    <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distinct Count in Query (Access 97, Win 95/98)

    You can do it in SQL Server (which might have been where there example was) but not in Access 97 and, as far as I know, not in Access 2000. Access SQL doesn't support the DISTINCT keyword in an aggregate function.
    Charlotte

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Distinct Count in Query (Access 97, Win 95/98)

    Cecilia,

    See Charlotte's reply... I tried various way, but, couldn't do what you request in one query... got the same error messages you indicated. I can think of some ways to program this, but it would be much easier to do it with two queries. Good Luck.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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