Numbering duplicate StockCodes

[kcludick1001]

I have a query with the following fields: StockCode, Description, Supplier, SupCatalogueNum.
A stockcode can be purchased from more than one supplier. How do I number the suppliers from 1 to 5 on the same stock code

Exp: StockCode: 020-01250-0120016
Supplier: ALE003
Supplier: NJR001
Supplier: STA002

I need to add colum with Supplier No:
Supplier No
ALE003 1
NJR001 2
STA002 3

[PhilOfWalton]

I suspect your database is not normalised

You should have a table of Suppliers and a table of StockCodes.
The Foreign Key in the table of StockCodes points to the Supplier

Phil

[nico5038]

The sequence number is indeed not normalized and can be calculated by counting the n'th record for a stock code.
For this a DCOUNT("supplier","tblStock", "[stockcode] = '" [stockcode] & "' and Supplier<='" [Supplier] & "'") can be used to count the number of suppliers within the stock item with the same or lower suppliercode. It will however costs a lot of processing time (it's in general slooooow).

Nic;o)

[kcludick1001]

My query result looks like this
StockCode Description Supplier SupCatalogueNum
020-01250-0120016 Sht MS CR 2.5x1.2mx1.6mm ALE003 10018500
020-01250-0120016 Sht MS CR 2.5x1.2mx1.6mm NJR001
020-01250-0120016 Sht MS CR 2.5x1.2mx1.6mm STA002 RCR2450122516
020-01250-0120016 Sht MS CR 2.5x1.2mx1.6mm STE003 C/R 2500x1200
020-01250-0120020 Sht MS CR 2.5x1.2mx2.0mm ALE003 10018508
020-01250-0120020 Sht MS CR 2.5x1.2mx2.0mm KAR001 EC & WHITE POW COUT
020-01250-0120020 Sht MS CR 2.5x1.2mx2.0mm NJR001 SCR02012252450
020-01250-0120020 Sht MS CR 2.5x1.2mx2.0mm STA002 RSCR245012252.0
020-01250-0120020 Sht MS CR 2.5x1.2mx2.0mm STE003 C/R 2500x1200

I need a Number colum numberbing the suppliers per stockcode.

[nico5038]

By adding a DCOUNT() function (see above) in a column of the select statement you can create this number dynmically. Just try !

Nic;o)

[kcludick1001]

Thanks nico5038...I managed to come right with the increment function. :-) kcludick1001

[nico5038]

Well done ! It will be slow and when you use a report you can add a field with a +1 function to achieve the same result.

Nic;o)