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

Topic

New Reply

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

Replies

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

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

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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

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

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

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