Help - Populating a field in a datafeed based on values in another field

[skinnybloke]

Hi - I'm trying to create a datafeed from a Microsoft Access database
but I am having a problem due to my lack of experience.

I have to populate a field with a delivery cost but this is not held
on the database.

What is held on the database is the price of the product.

The delivery cost is based on the price of the product.

A product costing up to £20 will cost £1.50 delivery.
A product costing more than £20.00 will cost £4.50 delivery.

I assume that this can be done using VB code in a module.

Any help would be appreciated.

[Bernard Peek]

In message <ps********************************@4ax.com>, skinnybloke
<th**************@yahoo.co.uk> writes

Hi - I'm trying to create a datafeed from a Microsoft Access database
but I am having a problem due to my lack of experience.

I have to populate a field with a delivery cost but this is not held
on the database.

What is held on the database is the price of the product.

The delivery cost is based on the price of the product.

A product costing up to £20 will cost £1.50 delivery.
A product costing more than £20.00 will cost £4.50 delivery.

I assume that this can be done using VB code in a module.

That's one way. Another way is to add a calculated field to a view

DelCost: iif(ProdCost >20.0, 4.5, 1.5)

You can then easily change the threshold and delivery charges.

One word of warning. This approach will recalculate the cost each time
the query is run. For invoicing purposes you should store the actual
value charged rather than relying on recalculating the figure each time
it is referenced. The formula may change and the new formula would give
a different result.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[skinnybloke]

Hi Bernard - I want this to work for a query and not a view. Can I put
this into a SQL statement?
On Tue, 1 Nov 2005 16:27:17 +0000, Bernard Peek <ba*@shrdlu.com>
wrote:

In message <ps********************************@4ax.com>, skinnybloke
<th**************@yahoo.co.uk> writes

Hi - I'm trying to create a datafeed from a Microsoft Access database
but I am having a problem due to my lack of experience.

I have to populate a field with a delivery cost but this is not held
on the database.

What is held on the database is the price of the product.

The delivery cost is based on the price of the product.

A product costing up to £20 will cost £1.50 delivery.
A product costing more than £20.00 will cost £4.50 delivery.

I assume that this can be done using VB code in a module.

That's one way. Another way is to add a calculated field to a view

DelCost: iif(ProdCost >20.0, 4.5, 1.5)

You can then easily change the threshold and delivery charges.

One word of warning. This approach will recalculate the cost each time
the query is run. For invoicing purposes you should store the actual
value charged rather than relying on recalculating the figure each time
it is referenced. The formula may change and the new formula would give
a different result.

[Bernard Peek]

Hi Bernard - I want this to work for a query and not a view. Can I put
this into a SQL statement?

Sorry, I forgot which database I was working with. Queries and views are
the same thing. So yes, it will work in a query.

In newsgroups I find that it's usually best to post replies below the
text they are commenting on, and to snip any non-essential part of the
message you are replying to.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[skinnybloke]

On Tue, 1 Nov 2005 23:49:40 +0000, Bernard Peek <ba*@shrdlu.com>
wrote:

Hi Bernard - I want this to work for a query and not a view. Can I put
this into a SQL statement?

Sorry, I forgot which database I was working with. Queries and views are
the same thing. So yes, it will work in a query.

Hi Bernard - this has worked a treat - thanks for your help.

David