Complicated Access SQL Query exclusion

Question

For an access database that looks like this: (All text fields)

Co1 Co2 Co3 Co4
A   k t N1
B   k t N2
A   m t N3
B   k z N4
A   k z N5
C   m t N6
C   k z N7
C   k t N8
A   k t N9
C   m t N10

I need to create some kind of reports that would do the following:

The results needs to select rows:

1. Ordered by Co1 first
2. then ordered by Co2
3. only include data where Co3 = t or r
4. when Co3 is NOT t nor r, then all other rows with a prior C04 where Co1 AND Co2 were the same as the current row are also excluded

1,2 and 3 are extremely easy to do (Order by and Where clause respectively) but can #4 be done in access SQL? If it can't, I'll make a small C# app that loops through the data but I feel this must be possible to do in SQL and if it can that would definately be much better.

The actual query and database is more complicated but #4 is where I get stuck.

With the example I gave the result would be:

A   m t N3
A   k t N9
C   m t N6
C   m t N10
C   k t N8

I believe this cover all possible cases.

Precision, we use the fourth column (C4 in this example) to determine the order but it is a text field with characters and digits in it. The field is a letter followed by 2 digits: A01, A02,..A99, B01, etc... Precision #2, I'm not the one to blame for this database design. :P

So, to the SQL guru's among us, can this be done in access SQL? How?

Thank you very much for any help you can provide.

Answer 1

If I read you correctly:

when Co3 is NOT t nor r, then all other rows with a prior C04 where Co1 AND Co2 were the same as the current row are also excluded

That is equivalent to excluding rows for which a later row with certain conditions exist. You can do that with a NOT EXISTS clause:

select *
from YourTable cur
where cur.Co3 in ('t','r')
and not exists (
    select *
    from YourTable later
    where cur.Co1 = later.Co1 
    and cur.Co2 = later.Co2
    and later.Co3 not in ('t','r') 
    and CInt(Mid(later.Co4,2)) > CInt(Mid(cur.Co4,2))
)
order by cur.Co1, cur.Co2 desc, CInt(Mid(cur.Co4,2))

Now N10 is not bigger than N5 normally. That's what the CInt(Mid(...,2)) is for: it turns N10 into the number 10, and the number N5 into 5.

Answer 2

A modification of Andomar's query which takes account of the values in Co4 starting with different letters by using the ASCII value of the character code to determine order. I'm assuming the range is A01...A99, B01...B99 etc and that B01 should be considered "later" than A99.

select *
from Table1 cur
where not exists (
    select 1
    from Table1 later
    where cur.Co1 = later.Co1 
    and cur.Co2 = later.Co2
    and later.Co3 not in ('t','r') 
    and (asc(left(later.Co4,1)) * 100) + CInt(Mid(later.Co4,2)) > (asc(left(cur.Co4,1)) * 100) + CInt(Mid(cur.Co4,2))
)
and cur.Co3 in ('t','r')
order by cur.Co1, cur.Co2

Answer 3

Not an answer, yet, but setup code for fellow answerers.

create table #boost (
  Co1 char(1),
  Co2 char(1),
  Co3 char(1),
  Co4 char(3)
)

insert into #boost values ('A', 'k', 't', 'N1')
insert into #boost values ('B', 'k', 't', 'N2')
insert into #boost values ('A', 'm', 't', 'N3')
insert into #boost values ('B', 'k', 'z', 'N4')
insert into #boost values ('A', 'k', 'z', 'N5')
insert into #boost values ('C', 'm', 't', 'N6')
insert into #boost values ('C', 'k', 'z', 'N7')
insert into #boost values ('C', 'k', 't', 'N8')
insert into #boost values ('A', 'k', 't', 'N9')
insert into #boost values ('C', 'm', 't', 'N10')