Add apportioned charge to variable number of orders?

[optionone]

Hi,

Not sure if this would be best tackled as a query but struggling with how else I could go about doign it. What I want to do is add a £100 charge to orders that are delivered - but I want to build this in to the price of the item ordered rather than showing it as a delivery charge on a new line.

Unfortunately the data I work off is by client by order item - so even though some clients only have one order item - others have 2 or 3. So I need some way of splitting the delivery charge dependent on number of items ordered, whilst also keeping the order item info intact (eg not grouped in to a total client order cost).

Thanks in advance!

 

[KenHigg]

I would suggest doing this with a DAO recordset.

 

[JamesMcS]

Or an update query might work - update [item price] to £100/(count * from orders where clientid=me.clientid and delivered=true)

Or something.... you get the idea

 

[gemma-the-husky]

i would have thought a double query

one to evaluate the allocation etc

another one to update where necessary.

if you can do it that way, its easier than iterating a recordset

 

[JamesMcS]

Definitely! Recordsets are the next step on the learning curve for me!

 

[optionone]

Think ive got a working solution with update queries

Will look in to understanding recordsets better though - thanks for the help

 

[KenHigg]

In the bigger picture I think I would approach this a bit differently. Maybe have a shipping charge per item field and on the invoice add this to the cost of the item for display. This would help in several different ways. One benifit is it would make it easier to figure out what happened after the fact like seeing what the different costs actually were...

Hope ths makes sense.