Complex Query

[alex]

Hello experts,

Using Access ’03…

I’m trying to perform a query that’s rather complicated (at least to
me).

I have a table that provides the history actions on a particular case
(there are thousands of cases). This table lists the same case number
multiple times with the applicable action and date of each action.
You’ll find a snapshot below:

CASE ACTION ACT_DATE
XXX1234 9 200801091251
XXX1234 16 200711270731
XXX1234 6 200711231055
XXX1234 4 200710101438
XXX1234 16 200709101311
XXX1234 3 200707260650
XXX1234 1 200601262031

I’ve already isolated every CASE that has a ‘4’ ACTION after a ‘16’;
i.e., you won’t find a CASE with an ACTION of 4 and an ACT_DATE
earlier than any 16 (which is what I want).

What I need to do is find every case with an action of 16 subsequent
to an action of 4 with any action in between except 7-15 (like the
example case above). Something like = ACTION of 16, any number except
7-15, ACTION of 4, any number except 7-15, ACTION of 16.

What I would not want:

CASE ACTION ACT_DATE
XXX1235 9 200801091251
XXX1235 16 200711270731
XXX1235 10 200711231055
XXX1235 4 200710101438
XXX1235 16 200709101311
XXX1235 3 200707260650
XXX1235 1 200601262031

Also what I would not want:

CASE ACTION ACT_DATE
XXX1236 9 200801091251
XXX1236 4 200711270731
XXX1236 16 200711231055
XXX1236 3 200710101438
XXX1236 16 200709101311
XXX1236 3 200707260650
XXX1236 1 200601262031

Hope I haven’t confused too many people.

Thanks,
alex

 

[Allen Browne]

Save this as (say) Query1:

SELECT Table1.[Case],
(SELECT Min(Act_Date) AS Min4Date
FROM Table1 AS Dupe
WHERE Dupe.[Case] = Table1.[Case]
AND Dupe.Action = 4) AS Min4Date,
(SELECT Max(Act_Date) AS Max16Date
FROM Table1 AS Dupe
WHERE Dupe.[Case] = Table1.[Case]
AND Dupe.Action = 16) AS Max16Date
FROM Table1
GROUP BY Table1.[Case]
HAVING (SELECT Min(Act_Date) AS Min4Date
FROM Table1 AS Dupe
WHERE Dupe.[Case] = Table1.[Case]
AND Dupe.Action = 4) <
(SELECT Max(Act_Date) AS Max16Date
FROM Table1 AS Dupe
WHERE Dupe.[Case] = Table1.[Case]
AND Dupe.Action = 16);

That should give you each distinct case, with the date of the earliest
action 4, and the date of the latest action 16, and eliminate those that
don't have both dates (comparison with null), or where the earliest 4 is
after (or same date as) the latest 16.

From there you can create another query to find those that have any action
7-15 between those dates.

SELECT Query1.[Case]
FROM Query1
WHERE NOT EXISTS
(SELECT Action
FROM Table1 AS Dupe
WHERE Dupe.[Case] = Query1.[Case]
AND Dupe.Action Between 7 And 15
AND Dupe.Act_Date Between Query1.Min4Date
And Query1.Max16Date);

That's completely untested, but hopefully enough to guide you down a useful
path. From memory, you can just use the aliases (Min4Date and Max16Date) in
the HAVING clause (i.e. you have to repeat the subqueries.)

If subqueries are a new concept, here's an intro:
http://allenbrowne.com/subquery-01.html

It's also a shame about the field named Case. Reserved word:
http://allenbrowne.com/AppIssueBadWord.html
The square brackets will probably save the day.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Hello experts,

Using Access ’03…

I’m trying to perform a query that’s rather complicated (at least to
me).

I have a table that provides the history actions on a particular case
(there are thousands of cases). This table lists the same case number
multiple times with the applicable action and date of each action.
You’ll find a snapshot below:

CASE ACTION ACT_DATE
XXX1234 9 200801091251
XXX1234 16 200711270731
XXX1234 6 200711231055
XXX1234 4 200710101438
XXX1234 16 200709101311
XXX1234 3 200707260650
XXX1234 1 200601262031

I’ve already isolated every CASE that has a ‘4’ ACTION after a ‘16’;
i.e., you won’t find a CASE with an ACTION of 4 and an ACT_DATE
earlier than any 16 (which is what I want).

What I need to do is find every case with an action of 16 subsequent
to an action of 4 with any action in between except 7-15 (like the
example case above). Something like = ACTION of 16, any number except
7-15, ACTION of 4, any number except 7-15, ACTION of 16.

What I would not want:

CASE ACTION ACT_DATE
XXX1235 9 200801091251
XXX1235 16 200711270731
XXX1235 10 200711231055
XXX1235 4 200710101438
XXX1235 16 200709101311
XXX1235 3 200707260650
XXX1235 1 200601262031

Also what I would not want:

CASE ACTION ACT_DATE
XXX1236 9 200801091251
XXX1236 4 200711270731
XXX1236 16 200711231055
XXX1236 3 200710101438
XXX1236 16 200709101311
XXX1236 3 200707260650
XXX1236 1 200601262031

Hope I haven’t confused too many people.

Thanks,
alex

 

[alex]

Save this as (say) Query1:

SELECT Table1.[Case],
(SELECT Min(Act_Date) AS Min4Date
  FROM Table1 AS Dupe
  WHERE Dupe.[Case] = Table1.[Case]
  AND Dupe.Action = 4) AS Min4Date,
(SELECT Max(Act_Date) AS Max16Date
  FROM Table1 AS Dupe
  WHERE Dupe.[Case] = Table1.[Case]
  AND Dupe.Action = 16) AS Max16Date
FROM Table1
GROUP BY Table1.[Case]
HAVING (SELECT Min(Act_Date) AS Min4Date
  FROM Table1 AS Dupe
  WHERE Dupe.[Case] = Table1.[Case]
  AND Dupe.Action = 4) <
(SELECT Max(Act_Date) AS Max16Date
  FROM Table1 AS Dupe
  WHERE Dupe.[Case] = Table1.[Case]
  AND Dupe.Action = 16);

That should give you each distinct case, with the date of the earliest
action 4, and the date of the latest action 16, and eliminate those that
don't have both dates (comparison with null), or where the earliest 4 is
after (or same date as) the latest 16.

From there you can create anotherqueryto find those that have any action
7-15 between those dates.

SELECT Query1.[Case]
FROM Query1
WHERE NOT EXISTS
  (SELECT Action
  FROM Table1 AS Dupe
  WHERE Dupe.[Case] = Query1.[Case]
  AND Dupe.Action Between 7 And 15
  AND Dupe.Act_Date Between Query1.Min4Date
    And Query1.Max16Date);

That's completely untested, but hopefully enough to guide you down a useful
path. From memory, you can  just use the aliases (Min4Date and Max16Date) in
the HAVING clause (i.e. you have to repeat the subqueries.)

If subqueries are a new concept, here's an intro:
   http://allenbrowne.com/subquery-01.html

It's also a shame about the field named Case. Reserved word:
   http://allenbrowne.com/AppIssueBadWord.html
The square brackets will probably save the day.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html

Hello experts,

Using Access ’03…

I’m trying to perform aquerythat’s rather complicated (at least to
me).

I have a table that provides the history actions on a particular case
(there are thousands of cases).  This table lists the same case number
multiple times with the applicable action and date of each action.
You’ll find a snapshot below:

CASE ACTION ACT_DATE
XXX1234 9 200801091251
XXX1234 16 200711270731
XXX1234 6 200711231055
XXX1234 4 200710101438
XXX1234 16 200709101311
XXX1234 3 200707260650
XXX1234 1 200601262031

I’ve already isolated every CASE that has a ‘4’ ACTION after a ‘16’;
i.e., you won’t find a CASE with an ACTION of 4 and an ACT_DATE
earlier than any 16 (which is what I want).

What I need to do is find every case with an action of 16 subsequent
to an action of 4 with any action in between except 7-15 (like the
example case above).  Something like = ACTION of 16, any number except
7-15, ACTION of 4, any number except 7-15, ACTION of 16.

What I would not want:

CASE ACTION ACT_DATE
XXX1235 9 200801091251
XXX1235 16 200711270731
XXX1235 10 200711231055
XXX1235 4 200710101438
XXX1235 16 200709101311
XXX1235 3 200707260650
XXX1235 1 200601262031

Also what I would not want:

CASE ACTION ACT_DATE
XXX1236 9 200801091251
XXX1236 4 200711270731
XXX1236 16 200711231055
XXX1236 3 200710101438
XXX1236 16 200709101311
XXX1236 3 200707260650
XXX1236 1 200601262031

Hope I haven’t confused too many people.

Thanks,
alex

Thanks Allen for your help...I'll give it a try.

alex