SQL Statement


T

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

Advertisements

J

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?
 
T

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?
 
J

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

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"
 
J

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
 
Ad

Advertisements

T

tsluu

Thanks Jahn.

works great!

John Spencer said:
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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top