MAX query - help!!!!

[crmarrio]

Please help if you can.... (before I go insane!)

We have a table of addresses for people (linked on PersonCode). The relationship is a one-to-many link.

Tbl Addresses (a linked Oracle table) has the following fields:

AddressID (primary key)
PersonCode (linked)
AddressType (S, P, T or E)
Start Date
End Date
AddressLine1,2,3,4,
Town

There can be more than one address per PersonCode, but never more than one address per address_type where enddate is null.

Here's the problem...

We want to extract 1 current address (i.e. with a null end date) that is either type T or P, but preferably T if it exists.

We can do one query to find the Max of the address type per PersonCode, but can I pull the address out in the same query? If you use anything after the Max, then it is not necessarily referring to the same record selected by the Max.

At the moment, we have to run a second query based on the first to do this, but as the Table is very large and based on Oracle, it takes quite a while to run, especially as it is based on a Query rather than a table.

Any ideas ? If only access could give me the results for that line after the Max part...

 

[FoFa]

Select * from Addresses
Where addresstype = 'T'
UNION
select * from Addresses
Where addresstype = 'P'
and PersonCode not exist (Select PersonCode from Addresses where Addresstype = 'T')

OR

Select * from Addresses
where Addresstype = (select MAX(A.Addresstype) from Addresses as A where Addresses.personcode = A.personcode)

Don't forget to include your END DATE = Null in those also