Help with a query involving duplicates

[maudley]

Hi I'm struggling with the following query, if I'm honest I'm not sure where to start with it:


Original table:

|Poskey|Date | Action|
|1 |05/06/2011|Ballast |
|1 |05/06/2011|Sleeper|
|1 |05/06/2011|Rail |
|1 |23/02/2009|Ballast |
|2 |06/02/2008|Ballast |
|2 |06/02/2008|Rail |
|3 |20/05/2009|Ballast |
|3 |20/05/2009|Sleeper|
|3 |20/05/2009|Rail |
|3 |18/02/2008|Rail |
|4 |15/01/2000|Ballast |
|4 |15/01/2000|Sleeper|
|4 |15/01/2000|Rail |


I would like a query which tells me which "Poskeys" Have had the "Action" Ballast and Rail and Sleeper all on the same date. An "Action" may have occurred at a prior or later date, it doesn't matter as long as all three actions have occurred on the same date.

Also the the action must have occurred after the year 2001 therefore using the above table the query would select these "Poskey" values:

1 and 3

Thanks in advance,
Matt

 

[Beetle]

Something along these lines is probably what you need. BTW, you should not use the word "Date" as the name of any field in your application as it is a reserved word in Access. For more on reserved words see;

http://allenbrowne.com/AppIssueBadWord.html

Here is some example SQL (I've changed the name of your date field here);

SELECT Poskey, YourDate
FROM YourTable As A, YourTable As B
WHERE A.YourDate = B.YourDate And A.Action <> B.Action And Year(A.YourDate) > 2001
GROUP BY A.Poskey, A.YourDate
HAVING Count(A.Action) > 2

 

[marlan]

Hi!
My idea would be to create 3 queries:
Ballasts:

SELECT * FROM YourTableName WHERE Action = "Ballast" AND Yaer([Date])>2001

Sleepers:

SELECT * FROM YourTableName WHERE Action = "Sleeper" AND Yaer([Date])>2001

Rails:

SELECT * FROM YourTableName WHERE Action = "Rail" AND Yaer([Date])>2001

An other query would join the Three queries on Poskey and Date (let the qury builder help you on that). All Poskeys in this query should match the conditions.

Well, I now see Beetle's answerd in a nicer way...

 

[maudley]

Something along these lines is probably what you need. BTW, you should not use the word "Date" as the name of any field in your application as it is a reserved word in Access. For more on reserved words see;


Here is some example SQL (I've changed the name of your date field here);

SELECT Poskey, YourDate
FROM YourTable As A, YourTable As B
WHERE A.YourDate = B.YourDate And A.Action <> B.Action And Year(A.YourDate) > 2001
GROUP BY A.Poskey, A.YourDate
HAVING Count(A.Action) > 2

Thank you to both of you for your ideas, the above query worked perfectly.

Thanks,
M