By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a

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.

Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.