Create Sequential Numbering Pattern from Date

[wbw]

I have a database of products in which I have determined when the product sold. The table has two fields ITEM and SOLD. Each product’s first selling date may be different and may not have sold in every week. I need to assign a sequential numbering pattern since the product was first introduced. A new field, SEQNO, needs to be created to show 1 for the first time, 2 for the second time, 3 for the third time… etc. I would like to benchmark new products performance against historical product introductions. For example how is the product performing in its fourth week against similar products in their initial fourth week?

Item1
first sold 12-02-2006 therefore SEQNO = 1, second time sold (in third week) 12-16-2006 SEQNO = 3 and third time sold 12-23-2006 (in fourth week) SEQNO = 4... etc.

Item2
first sold 05-27-2006 therefore SEQNO = 1, second time sold (in fifth week) 06-24-2006 SEQNO = 5 and third time sold 07-29-2006 (in tenth week) SEQNO = 10... etc.

Item3
first sold 03-08-2008 therefore SEQNO = 1, second time sold (in second week) 03-15-2008 SEQNO = 2 and third time sold 03-22-2008 (in third week) SEQNO = 3... etc.

[wbw]

The SEQNO field needs to be a sequential number starting from the first sold date and increment by 7, for example 1 for the first week, 2 for the second week, 3 for the third week… regardless if it sold in those weeks or not.