Need help with Query

[tampaite]

I have the below table that am writing a query against:

MaxDate	ProfileID	EffectiveDate
02/02/2015	1234	1/1/2015
03/02/2015	1234	2/1/2015
?	1233	3/1/2015
02/04/2015	1233	4/1/2015
?	1333	5/1/2015
?	1333	6/1/2015
?	1345	7/1/2015
03/05/2014	1345	8/1/2015
03/05/2015	1345	8/2/2015

Here are the requirements:

1. If the MaxDate for a ProfileID is NULL then display Null in our case ?
2. If the MaxDate for a ProfileID has a date then display the max value

Expected Result:

MaxDate	ProfileID	EffectiveDate	seq
?	1345	7/1/2015	1
?	1333	6/1/2015	1
03/02/2015	1234	2/1/2015	1
?	1233	3/1/2015	1

My Query as below:

SELECT ProfileData.MaxDate, ProfileData.ProfileID, ProfileData.EffectiveDate,
(
SELECT COUNT(*)
FROM ProfileData AS ProfileData2
WHERE ProfileData2.ProfileID = ProfileData.ProfileID
AND ProfileData2.MaxDate < ProfileData.MaxDate
)


+ 1 AS seq
FROM ProfileData
ORDER BY ProfileData.ProfileID ASC



However, my issue is that seq = 1 is showing correct values for ProfileIDs 1233, 1333, 1345 and doesn't pick up the max date value for 1234.

MaxDate	ProfileID	EffectiveDate	seq
02/04/2015	1233	4/1/2015	2
?	1233	3/1/2015	1
03/02/2015	1234	2/1/2015	2
02/02/2015	1234	1/1/2015	1
?	1333	6/1/2015	1
?	1333	5/1/2015	1
03/05/2015	1345	8/2/2015	3
03/05/2014	1345	8/1/2015	2
?	1345	7/1/2015	1

Thanks for your help!

[rpeare]

you can get the MAXDATE in the format you want by using the formula:

iif(isdate([OriginalDateFieldName]), cdate([OriginalDateFieldName]), null)

As for the rest of it, what are your rules for the numbering? because it looks to me like 1333 is the problem because it has a duplicate sequence number where the others don't.