I tried using Max but ran into performance problems that in production render
max not an option. A more simple way, albeit not working, was to use top 1 and
order by lastdate desc and lastuser <> 'Master' This does work most of the
time????

This rubs me the wrong way as programming should be consistant!

I thought I had it, but I was only close. Here's what's happening....

I want the latest date that is NOT 'Master', but as you will see that is not
happening.

I populate a table (cont_main) with accountno's (PK) if any of the subtables
have a higher lastdate than my current lastdate. I do this because there is a
serious hit on the server running an all-in-wonder query.

Anyway, after I have my accountno's Inserted, I run an Update. I then go to
each sub table (1:many) and get the lastdate that is greater than my current
lastdate and the user is not 'Master'. So for example here is my test select
query for the contact history table... After I get this to work I'll re-write
it as an update query.

select distinct cm.accountno ,ch.accountno
,(select top 1 ch.lastdate
from conthist ch
where ch.accountno = cm.accountno
order by lastdate desc)[chLastDate]

,(select top 1 ch.lastuser
from conthist ch
where ch.accountno = cm.accountno
order by lastdate desc)[chLastUser]
from
conthist ch
join
cont_main cm
on ch.accountno = cm.accountno

where (select top 1 ch.lastuser
from conthist ch
order by lastdate desc) not in('MASTER')

This returns....

A1053067318$C!# Pow A1053067318$C!# Pow 7/23/2002 MASTER
A1053067320$TYT& Fai A1053067320$TYT& Fai 7/23/2002 MASTER
A1053067322(CP;: Dep A1053067322(CP;: Dep 7/23/2002 MASTER
A1053067515%9\=R Key A1053067515%9\=R Key 7/23/2002 MASTER
A1053067626&\ELM Dun A1053067626&\ELM Dun 7/23/2002 MASTER
A1053067632(7,L; Pat A1053067632(7,L; Pat 7/23/2002 MASTER
A1053067643$=TPF Pop A1053067643$=TPF Pop 7/23/2002 MASTER
A1053067689$Y${O Shi A1053067689$Y${O Shi 7/23/2002 MASTER
A1053067698*Z ,L Vul A1053067698*Z ,L Vul 7/23/2002 MASTER
A1053067726 I:M4 Gan A1053067726 I:M4 Gan 7/23/2002 MASTER
A1053067896$<&`P Wea A1053067896$<&`P Wea 7/23/2002 MASTER
A1061562722$&1VL Tow A1061562722$&1VL Tow 5/2/2002 MASTER



TIA

JeffP....