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

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Distinct Count in Query (Access 97, Win 95/98)

    Author
    Topic
    WSCecilia
    AskWoody Lounger
    July 12, 2002 at 5:43 pm #373553

    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 🙂

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSbushaw
      AskWoody Lounger
      July 12, 2002 at 6:04 pm #600815

      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:

      SELECT Workday, Count(EmpNumber) AS NumberOfEmp
      FROM tblWorkdays
      GROUP BY Workday;

      Using your example data, this produces the following output:

      Workday NumberOfEmp
      Monday 1
      Sunday 2
      Tuesday 1

      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.

      Reply | Quote
      • WSCecilia
        AskWoody Lounger
        July 12, 2002 at 6:09 pm #600816

        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.

        Reply | Quote
        • WSaccdb
          AskWoody Lounger
          July 12, 2002 at 6:54 pm #600826

          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

          Reply | Quote
          • WSCecilia
            AskWoody Lounger
            July 12, 2002 at 7:13 pm #600835

            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….

            dizzy

            Reply | Quote
        • WSaccdb
          AskWoody Lounger
          July 12, 2002 at 7:07 pm #600832

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

          Reply | Quote
          • WSCecilia
            AskWoody Lounger
            July 12, 2002 at 7:33 pm #600845

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

            Reply | Quote
        • WSbushaw
          AskWoody Lounger
          July 12, 2002 at 7:17 pm #600837

          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:

          SELECT Count(EmpNumber) as CountEmp
          FROM (SELECT DISTINCT EmpNumber FROM tblWorkdays);

          which generates a result of 2 (for your example data), but this syntax apparently is not allowed in Access 97….

          Reply | Quote
          • WSCecilia
            AskWoody Lounger
            July 12, 2002 at 7:35 pm #600846

            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….

            eyeout

            Reply | Quote
            • WScharlotte
              AskWoody Lounger
              July 13, 2002 at 5:04 am #600905

              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.

              Reply | Quote
    • WSGARYPSWANSON
      AskWoody Lounger
      July 12, 2002 at 6:38 pm #600821

      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.

      Reply | Quote
      • WSCecilia
        AskWoody Lounger
        July 12, 2002 at 7:06 pm #600831

        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.

        Reply | Quote
        • WSGARYPSWANSON
          AskWoody Lounger
          July 15, 2002 at 11:09 am #601149

          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.

          Reply | Quote
    Viewing 1 reply thread
    Reply To: Distinct Count in Query (Access 97, Win 95/98)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.