functions / queries

[merlin2049er]

I've got a few lookup queries that return one result (for conversions), I've been trying to use them in a calculated field in another table.

How would I go about using that result in a calculated field function?

Any suggestions.



Thanks,
Joe

[June7]

What do you mean by 'lookup queries'?

How are you trying to use in calculated field?

Calculated field in table cannot call custom functions.

[merlin2049er]

Right, that's why I've been having problems. I've got a conversion table that has one record with 3 fields in it. I need to lookup each of these fields for a calculation in another table.

What would you recommend?

[orange]

Joe,

Readers are not familiar with you or your environment. Can you describe to us in plain, simple English what you are trying to do? The simpler the better, and once readers understand your requirement and situation, I'm sure more focused responses will evolve.

Also, an example showing input and expected outputs is often helpful.

[merlin2049er]

I've got values (3 lookup values) in the global rates table, that I want to use to calculate fields in the UK products table (3 calculated fields) .

Wondering how I should go about it.

Thanks,
Joe

[June7]

Build a query of the UK table and do calcs in query.

Or build a report and do calcs in textboxes.

[orange]

What are the units for ShippingCost? US Dollars? GBP?
Global rates change frequently, how do you account for the changes?

You have shown us a row in a table (UKProduct) that includes costs in USD. But what exactly are you trying to do? You don't need to store all currencies for all products. You can work in your local currency and convert when required via the Rates table.

Often people will have a table of Currency/Exchange Rates.
They will record their inventory in one common/local currency.
They will identify shipping charges in some common/local currency.
When an item is sold, they will work with the common/local currency, join to the Currency/Exchange Rates table in order to get the Rate in the desired currency.

eg A Book costs 12.5 kilowhatzits

CurrencyExchangeRates (fictitious for illustration only)
1 kilowhatzit ------>  23.75 US Dollars
                   ------>  18.86 CDN Dollars
                   ------> 200    Faroe Island Kroner
                   ------> 327.8 Zuyang Pesos

The arithmetic

If 1 kilowhatzit is equivalent to 23.75 US dollars
then 12.5 kilowhatzits is equivalent to (12.5 * 23.75) = 296.875 US Dollars

Since the Rate table can change often, the rates may be replaced/updated daily/weekly.

[merlin2049er]

Thanks, so I'll move those calculated fields from the product table and put them in a query and join it as a subform of the product. I'll try that.