How do you intend to use that Sequence number once it's been assigned? There are a few ways to do things, but some of them may be inappropriate depending on exactly what the business requirements are for those sequence numbers.
This is the nice, simple & efficient SQL 2k5 syntax:
DECLARE @mytable TABLE
, RequiredDate SMALLDATETIME
SELECT '001', '1-May-08'
UNION ALL SELECT '001', '5-May-08'
UNION ALL SELECT '001', '6-May-08'
UNION ALL SELECT '002', '1-May-08'
UNION ALL SELECT '003', '1-May-08'
UNION ALL SELECT '003', '3-May-08'
, sequence = ROW_NUMBER() OVER (
PARTITION BY AddressID
ORDER BY RequiredDate ASC
DDL = Data Definition Language. Basically can you create objects on the SQL Server? (in this case a view - this is very similar to an Access saved query)
The code is T-SQL - run it in SQL Server Management Studio, or use an Access Pass-through query, and you'll see how it works. All variables in T-SQL are prefixed "@" - that is a table variable.