SQL Statement

[tsluu]

tblTran
Empl# Date TranCode
1 02/08/2009 SICK
1 02/08/2009 AAL
1 05/08/2009 AAL
1 06/08/2009 WORK
1 06/08/2009 OT
1 08/08/2009 REST
1 08/08/2009 AAL

Result:

Empl# Date TranCode
1 02/08/2009 SICK
1 08/08/2009 REST

Can anyone help with the SQL query to give the above results.
I want only transactions with count more than 1 for days with "AAL" as the
combinations.

 

[John W. Vinson]

tblTran
Empl# Date TranCode
1 02/08/2009 SICK
1 02/08/2009 AAL
1 05/08/2009 AAL
1 06/08/2009 WORK
1 06/08/2009 OT
1 08/08/2009 REST
1 08/08/2009 AAL

Result:

Empl# Date TranCode
1 02/08/2009 SICK
1 08/08/2009 REST

Can anyone help with the SQL query to give the above results.
I want only transactions with count more than 1 for days with "AAL" as the
combinations.

I'm not understanding your question.

"with count more than one for days with "AAL" as the combinations"

doesn't parse for me. What do you mean by "the combinations"? How do you
decide to include "REST" but exclude "OT"? What's the logic?

 

[tsluu]

I'm not understanding your question.

"with count more than one for days with "AAL" as the combinations"

doesn't parse for me. What do you mean by "the combinations"? How do you
decide to include "REST" but exclude "OT"? What's the logic?

 

[John W. Vinson]

On any day with more than 1 transactions and only with "AAL" code

Is there a Primary Key to this table, or any other field which would uniquely
identify a record? You'll need a Subquery but unless you can select an
individual record, I can't immediately see how to do this.

 

[tsluu]

Hi John,

There is a primary key to this table.

So far this is what I got:
SELECT A.empl#, A.Date, A. trancode
FROM tblTran AS A INNER JOIN [SELECT empl#, Date, trancode
FROM tblTran
GROUP BY empl#, Date
HAVING count(trancode) >1] AS E ON (E.Date = A.Date) AND (E.empl# = A.empl#)

how do I filter out transactions with trancode="AAL"

 

[John Spencer]

Perhaps what you want is the following:

SELECT A.[empl#], A.[Date], A. trancode
FROM tblTran AS A INNER JOIN
[SELECT empl#, Date, trancode
FROM tblTran
GROUP BY empl#, tDate
HAVING count(trancode) >1]. AS E
ON (E.[Date] = A.[Date])
AND (E.[empl#] = A.[empl#])
WHERE A.trancode <> "AAL"

I have to say that I am surprised that you are not getting an error with that
SQL statement. Did you copy and past the SQL statement or just type it into
your post? It appears to be the latter, since in one place your refer to
tDate and other places you refer to Date. In addition, I would expect
brackets around Date, and Empl# since one is a reserved word and the other
contains a "special" character (Not a letter, number, or underscore). Plus
there was a missing period

It is always better to copy and paste - that way you show what is actually
being used and avoid typing errors that will confuse the issue.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[tsluu]

Thanks Jahn.

works great!

Perhaps what you want is the following:

SELECT A.[empl#], A.[Date], A. trancode
FROM tblTran AS A INNER JOIN
[SELECT empl#, Date, trancode
FROM tblTran
GROUP BY empl#, tDate
HAVING count(trancode) >1]. AS E
ON (E.[Date] = A.[Date])
AND (E.[empl#] = A.[empl#])
WHERE A.trancode <> "AAL"

I have to say that I am surprised that you are not getting an error with that
SQL statement. Did you copy and past the SQL statement or just type it into
your post? It appears to be the latter, since in one place your refer to
tDate and other places you refer to Date. In addition, I would expect
brackets around Date, and Empl# since one is a reserved word and the other
contains a "special" character (Not a letter, number, or underscore). Plus
there was a missing period

It is always better to copy and paste - that way you show what is actually
being used and avoid typing errors that will confuse the issue.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

There is a primary key to this table.

So far this is what I got:
SELECT A.empl#, A.Date, A. trancode
FROM tblTran AS A INNER JOIN [SELECT empl#, Date, trancode
FROM tblTran
GROUP BY empl#, Date
HAVING count(trancode) >1] AS E ON (E.Date = A.Date) AND (E.empl# = A.empl#)

how do I filter out transactions with trancode="AAL"