Dear old hand,

Thank you for your response! What I actually want is to calculatie the number of items the customer has to buy:

itemBalk1Stof1Stof2Stof3Stof4Stof5Stof6Stof7Stof8

number of items needed46244116124

length of each item (mm)5138,15147820002698,241479,36335080013331473

available length (mm)600060006000600060006000600060006000

available length (mm)700070007000NULLNULLNULLNULLNULLNULL

available length (mm)900090009000NULLNULLNULLNULLNULLNULL

Suggested length600090006000600060006000600060006000

nr of items to buy411211331[/color

The length they need are calculated and also the number of items they need. Now depending on the available lengths we have in stock, how should I calculate the nr of items to buy (I now did it with my head :))

Thanx in advance for all your help!

As I don't get a nice outlined table, i made another test table:

CREATE TABLE [dbo].[testtest](

[item] [varchar](50) NULL,

[balk1] [varchar](50) NULL,

[stof1] [varchar](50) NULL,

[stof2] [varchar](50) NULL,

[stof3] [varchar](50) NULL,

[stof4] [varchar](50) NULL,

[stof5] [varchar](50) NULL,

[stof6] [varchar](50) NULL,

[stof7] [varchar](50) NULL,

[stof8] [varchar](50) NULL,

) ON [PRIMARY]

GO

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('number of items needed', 4, 6, 2, 4, 4, 1, 16, 12, 4)

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('length of each item (mm)', 5138, 1478, 2000, 2698, 1479, 3350, 800, 1333, 1473)

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('available length (mm)', 6000, 6000, 6000, 6000, 6000, 6000, 6000, 6000, 6000)

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('available length (mm)', 7000, 7000, 7000, null, null, null, null, null, null)

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('available length (mm)', 9000, 9000, 9000, null, null, null, null, null, null)

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('Suggested length', 6000, 9000, 6000, 6000, 6000, 6000, 6000, 6000, 6000)

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)

values ('nr of items to buy', 4, 1, 1, 2, 1, 1, 3, 3, 1)