CODEWITHSUNDEEP
ms-access
Hani.H
Hani.H

Reputation: 38

aggregate functions in microsoft access

I have a table as follows:

userid  checktime                checktype
------------------------------------------
213     10/30/2014 2:06:09 PM    O
592     10/30/2014 2:28:04 PM    I
714     10/30/2014 10:02:59 AM   O
714     10/30/2014 10:03:01 AM   O
147     10/30/2014 1:05:15 PM    O
147     10/30/2014 1:05:20 PM    I
147     10/30/2014 1:13:17 PM    O
147     10/30/2014 1:13:23 PM    I
213     10/30/2014 11:56:11 AM   I
147     10/30/2014 1:08:26 PM    O
147     10/30/2014 1:08:32 PM    I

How can I select users who have record with checktype of 'I' only or with checktype of 'O' only, not with both? Please I want the query to be compatible with Microsoft Access.

Upvotes: 0

Views: 66

Answers (2)

user783388
user783388

Reputation:

You can use the fact that a boolean TRUE is represented as -1 and a boolean FALSE as 0. So you count how many times type="I" is true and how many times type="O" is true. Group that by the user ID and show only those who are not equal:

SELECT User, Sum([type]="i") AS ins, Sum([type]="o") AS outs, Abs(Sum([type]="i")-Sum([type]="o")) AS diff
FROM myTable
GROUP BY User
HAVING (((Abs(Sum([type]="i")-Sum([type]="o")))>0));

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Start by creating a query which returns one row for each combination of userid and checktype:

SELECT DISTINCT h.userid, h.checktype
FROM tblHani AS h
WHERE (((h.checktype) In ('I','O')));

Note if checktype can only be either I or O in all rows (no Nulls, zero-length string, or other values), that WHERE clause is not needed.

Then you can use that as a subquery source for another where you count the checktype values for each userid, and keep only those whose count is 1:

SELECT sub.userid, Count(sub.checktype) AS CountOfchecktype
FROM
    (
        SELECT DISTINCT h.userid, h.checktype
        FROM tblHani AS h
        WHERE (((h.checktype) In ('I','O')))
    ) AS sub
GROUP BY sub.userid
HAVING (((Count(sub.checktype))=1));

With your sample data in a table named tblHani, this is the output I get in Access 2010:

userid CountOfchecktype
------ ----------------
   592                1
   714                1

Upvotes: 1

Related Questions