Worksheets and records - accessing components

[IanH]

Apologies if half this post is somewhere else, I hit the wrong button and lost my previous post without finishing it.

I am having problems with worksheets giving me the results that I expect. This is under VW 11.5 on WinXP.

At a very simplistic level, I have created a record to allow me to cost materials. The record contains:

ID - Text

QTY - Number

PRICE - Number

Using the following simple example, hopefully someone can tell me if what I want to do is possible.

I have 3 individual components, each made up of half a plank of wood. The cost of one plank of wood is 10 and I can only buy it in whole planks. So I will be using 1 1/2 planks of wood, will need to buy 2 planks and the total cost will be 20.

In VW, I have 3 identical instances of the record.

ID - BEAM - the part identifier

REQ QTY - 0.5 - half a unit

UNIT PRICE - 10 - total unit price 10

What I am looking for is a worksheet that contains the ID a total quantity and a total price all grouped by the same ID.

So it should read.

ID REQ QTY UNIT PRICE ORDER QTY ORDER PRICE

BEAM 1.5 10 2 20

Order Qty and Order Price are derived as follows.

ORDER QTY = REQ QTY rounded up (ie ROUND(0.5+REQ QTY))

ORDER PRICE = ORDER QTY * UNIT PRICE

Unfortunately, It would appear that I have no control over whether I am using an individual instance of a field or I am using the sum of all the fields as grouped.

So if I group by ID I get:

ID REQ QTY UNIT PRICE ORDER QTY ORDER PRICE

BEAM 1.5 30 3 140 / 60

ORDER PRICE is either 140 or 60 depending on if I calculate it from ORDER QTY or from the "EXAMPLE.UNIT_PRICE" record field.

If I leave ungrouped I get 3 instances:

ID REQ QTY UNIT PRICE ORDER QTY ORDER PRICE

3 1.5 30 3 30

BEAM 0.5 10 1 10

BEAM 0.5 10 1 10

BEAM 0.5 10 1 10

Unfortunately neither examples are correct.

What I need to do is tell VW is:

Derive ORDER QTY from the sum of the REQ QTY field

Derive ORDER PRICE from a single record instance of UNIT PRICE * ORDER PRICE

But I cannot see how to do this (or even if it is possible). I don't want to change the meaning of price to mean the price of the instance as I don't want to have to keep calculating fractional values everytime that I use a different QTY.

Hopefully this is clear - it may need loadding in a fixed pitch font to get the columns to align.

Can anyone assist even if it is to say that this is impossible.

Thanks

[islandmon]

I used a Class 'Beam', (formulas are indicated under the cells) :