Tricky query - how??

[Julia Boswell]

Folks, I've got a query to run and I can't for the life of me work out how
to approach it... any ideas??

Here's an example of the records

Record SAASMSN UnitSN Applique BoardMarried BoardDivorced AppMarried
AppDivorced
1 12345 4444 1234 18/05/2005 18/05/2005 24/05/2005
3 12345 4444 18/05/2005 25/05/2005
43 12345 5432 25/05/2005 06/06/2005


I want to display the following fields in the query

SAASMSN, Board Married, Board Divorced

However (and here's the hard part...) I don't want to display duplicate
UnitSNs. In this case I want to display record 3 not record 1, as record 1
has been overwritten by record 3. If there are duplicate UnitSNs then it
will always be the case that the BoardMarried date is the same and the
AppMarried and AppDivorced fields will be NotNull. Does that make sense??

I know what I want logically, i.e. select all records except where UnitSN &
BoardMarried are identical, in which case select the record where AppMarried
and AppDivorced are null, but I have no idea how to word this, or whether it
needs more than one query, or should involve a make table query.

Help.........

Thanks in advance

Julia

 

[Michel Walsh]

Hi,


SELECT a.*
FROM myTable as a
WHERE a.record IN( SELECT TOP 1 b.record
FROM myTable As b
WHERE b.SAASMSN=a.SAASMSM
ORDER BY iif((BoardMarried Is NULL) AND (BoardDivorced
IS NULL), 0, 1)
)



basically, for a given SAASMSN, all the records (with that SAASMSN value)
are ordered, those with

(BoardMarried Is NULL) AND (BoardDivorced IS NULL)

appearing before any other. After the ordering did occur, the TOP 1 record
is kept, from which we retain only its record value.

We keep only the records that have seen their record value in the previous
treatment.


Hoping it may help,
Vanderghast, Access MVP

 

[Julia Boswell]

Thanks, I'll give that a try.

Julia

Hi,


SELECT a.*
FROM myTable as a
WHERE a.record IN( SELECT TOP 1 b.record
FROM myTable As b
WHERE b.SAASMSN=a.SAASMSM
ORDER BY iif((BoardMarried Is NULL) AND (BoardDivorced
IS NULL), 0, 1)
)



basically, for a given SAASMSN, all the records (with that SAASMSN value)
are ordered, those with

(BoardMarried Is NULL) AND (BoardDivorced IS NULL)

appearing before any other. After the ordering did occur, the TOP 1
record is kept, from which we retain only its record value.

We keep only the records that have seen their record value in the previous
treatment.


Hoping it may help,
Vanderghast, Access MVP