If Query

G

Guest

Access 2003: How would i write a formula or something within Criteria in a
query to say, If the currency field is "EUR", then divide the price field by
the relative conversion rate in order to make all data within the price field
converted to dollars.
For example. Take all records with Currency field as EUR and then divide all
the related price fields and divide them by 0.820!
Many Thanks,
Matt
 
N

Neil Sunderland

Matt said:
Access 2003: How would i write a formula or something within Criteria in a
query to say, If the currency field is "EUR", then divide the price field by
the relative conversion rate in order to make all data within the price field
converted to dollars.
For example. Take all records with Currency field as EUR and then divide all
the related price fields and divide them by 0.820!
Click to expand...

Marshall has already told you how to do this, y'know...

You need two tables:
tblPrices: fields Item, Price, Currency
tblCurrency: fields ConvertFrom, ConvertTo, Rate

Let's say tblPrices is:
Item Price Currency
Apples 1.00 GBP
Apples 1.10 SEK
Apples 1.20 EUR
Apples 1.30 FFR
Bananas 2.00 GBP
Bananas 2.20 SEK
Bananas 2.40 EUR
Bananas 2.60 FFR

And tblCurrency
ConvertFrom ConvertTo Rate
GBP USD 2.00
SEK USD 2.50
EUR USD 1.50
FFR USD 3.00

Then to convert everything from Pounds to Dollars:
SELECT
Item, Price, Rate, Price*Rate AS [Dollars]
FROM
tblPrices
INNER JOIN
tblCurrency ON ConvertFrom = Currency
WHERE
Currency = 'GBP' AND
ConvertTo = 'USD'

Will give you:
Item Price Rate Dollars
Apples 1.00 2.00 2.00
Bananas 2.00 2.00 4.00
 
G

Guest

Neil,

Apologies both to yourself and to Marshall but i was convinced there would
be an easier way within criteria but obviously not.
Many Thanks for this

Matt

Neil Sunderland said:
Matt said:
Access 2003: How would i write a formula or something within Criteria in a
query to say, If the currency field is "EUR", then divide the price field by
the relative conversion rate in order to make all data within the price field
converted to dollars.
For example. Take all records with Currency field as EUR and then divide all
the related price fields and divide them by 0.820!
Click to expand...

Marshall has already told you how to do this, y'know...

You need two tables:
tblPrices: fields Item, Price, Currency
tblCurrency: fields ConvertFrom, ConvertTo, Rate

Let's say tblPrices is:
Item Price Currency
Apples 1.00 GBP
Apples 1.10 SEK
Apples 1.20 EUR
Apples 1.30 FFR
Bananas 2.00 GBP
Bananas 2.20 SEK
Bananas 2.40 EUR
Bananas 2.60 FFR

And tblCurrency
ConvertFrom ConvertTo Rate
GBP USD 2.00
SEK USD 2.50
EUR USD 1.50
FFR USD 3.00

Then to convert everything from Pounds to Dollars:
SELECT
Item, Price, Rate, Price*Rate AS [Dollars]
FROM
tblPrices
INNER JOIN
tblCurrency ON ConvertFrom = Currency
WHERE
Currency = 'GBP' AND
ConvertTo = 'USD'

Will give you:
Item Price Rate Dollars
Apples 1.00 2.00 2.00
Bananas 2.00 2.00 4.00

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
G

Guest

Neil,

I still cannot get this to work. I have my original PO table with the item
(Contract ID) and then Currency and Price.
I have created the Currency table as you stated with the 3 fields but the
SQL comes up with an error.
I do not know SQL very well and the whole thing reads:

SELECT Acceptance.[Request ID], Acceptance.[Contract Quote Date],
Acceptance.[First Name], Acceptance.Surname, Acceptance.[Agent ID],
Acceptance.[Quote Sent], PO.[Agent ID], PO.CURRENCY, PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID], PO.[Serial ID] AS
[PO_Serial ID]
FROM Acceptance INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID];

SELECT
Contract ID, Price, Currency, Price*Rate AS [Dollars]
FROM
tblPO
INNER JOIN
tblCurrency ON ConvertFrom = Currency
WHERE
Currency = 'GBP' AND
ConvertTo = 'USD'

Matt

Matt Dawson said:
Neil,

Apologies both to yourself and to Marshall but i was convinced there would
be an easier way within criteria but obviously not.
Many Thanks for this

Matt

Neil Sunderland said:
Matt said:
Access 2003: How would i write a formula or something within Criteria in a
query to say, If the currency field is "EUR", then divide the price field by
the relative conversion rate in order to make all data within the price field
converted to dollars.
For example. Take all records with Currency field as EUR and then divide all
the related price fields and divide them by 0.820!
Click to expand...

Marshall has already told you how to do this, y'know...

You need two tables:
tblPrices: fields Item, Price, Currency
tblCurrency: fields ConvertFrom, ConvertTo, Rate

Let's say tblPrices is:
Item Price Currency
Apples 1.00 GBP
Apples 1.10 SEK
Apples 1.20 EUR
Apples 1.30 FFR
Bananas 2.00 GBP
Bananas 2.20 SEK
Bananas 2.40 EUR
Bananas 2.60 FFR

And tblCurrency
ConvertFrom ConvertTo Rate
GBP USD 2.00
SEK USD 2.50
EUR USD 1.50
FFR USD 3.00

Then to convert everything from Pounds to Dollars:
SELECT
Item, Price, Rate, Price*Rate AS [Dollars]
FROM
tblPrices
INNER JOIN
tblCurrency ON ConvertFrom = Currency
WHERE
Currency = 'GBP' AND
ConvertTo = 'USD'

Will give you:
Item Price Rate Dollars
Apples 1.00 2.00 2.00
Bananas 2.00 2.00 4.00

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
Click to expand...
 
N

Neil Sunderland

Matt said:
I still cannot get this to work. I have my original PO table with the item
(Contract ID) and then Currency and Price.
I have created the Currency table as you stated with the 3 fields but the
SQL comes up with an error.
Click to expand...

OK, this may take a little time, and several messages :)

Firstly, does your original query (the one below) work?

SELECT Acceptance.[Request ID], Acceptance.[Contract Quote Date],
Acceptance.[First Name], Acceptance.Surname, Acceptance.[Agent ID],
Acceptance.[Quote Sent], PO.[Agent ID], PO.CURRENCY, PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID], PO.[Serial ID] AS
[PO_Serial ID]
FROM Acceptance INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial
ID];

I have to ask, as I can't see a [Contract ID] anywhere in it. Perhaps
it's the [Request ID]?
 
G

Guest

Right,
The original query works yes no problems there.
I only used Contract ID to replace item as you had it in the previous table.
Contract ID can go in and out i was just unsure as to whether I needed it
Matt

Neil Sunderland said:
Matt said:
I still cannot get this to work. I have my original PO table with the item
(Contract ID) and then Currency and Price.
I have created the Currency table as you stated with the 3 fields but the
SQL comes up with an error.
Click to expand...

OK, this may take a little time, and several messages :)

Firstly, does your original query (the one below) work?

SELECT Acceptance.[Request ID], Acceptance.[Contract Quote Date],
Acceptance.[First Name], Acceptance.Surname, Acceptance.[Agent ID],
Acceptance.[Quote Sent], PO.[Agent ID], PO.CURRENCY, PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID], PO.[Serial ID] AS
[PO_Serial ID]
FROM Acceptance INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial
ID];

I have to ask, as I can't see a [Contract ID] anywhere in it. Perhaps
it's the [Request ID]?

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
N

Neil Sunderland

Matt said:
The original query works yes no problems there.
Click to expand...

I'm starting to get confused myself, now. :)

Let's start again, from scratch. Can you paste a working copy of your
query, that returns all the fields you want to see (except the
converted currency value), please?
 
G

Guest

I have been confused for a few hours, very stressful but will be ace when
done :)

Current SQL (just brings up fields):

SELECT Acceptance.[Request ID], Acceptance.[Contract Quote Date],
Acceptance.[First Name], Acceptance.Surname, Acceptance.[Agent ID],
Acceptance.[Quote Sent], PO.[Agent ID], PO.CURRENCY, PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID], PO.[Serial ID] AS
[PO_Serial ID]
FROM Acceptance INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID];

Matt
 
N

Neil Sunderland

Matt said:
SELECT Acceptance.[Request ID], Acceptance.[Contract Quote Date],
Acceptance.[First Name], Acceptance.Surname, Acceptance.[Agent ID],
Acceptance.[Quote Sent], PO.[Agent ID], PO.CURRENCY, PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID], PO.[Serial ID] AS
[PO_Serial ID]
FROM Acceptance INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID];
Click to expand...

I've only spent two minutes on this, so it might not work first time:

SELECT
Acceptance.[Request ID],
Acceptance.[Contract Quote Date],
Acceptance.[First Name],
Acceptance.Surname,
Acceptance.[Agent ID],
Acceptance.[Quote Sent],
PO.[Agent ID],
PO.CURRENCY,
PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID],
PO.[Serial ID] AS [PO_Serial ID],
tblPO.ConvertTo,
tblPO.Rate,
tblPO.Rate*PO.PRICE AS Converted
FROM
Acceptance
((INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID])
INNER JOIN tblPO ON Acceptance.CURRENCY = tblPO.ConvertFrom)

This assumes that the currency conversions are in a table call tblPO
with fields ConvertFrom, ConvertTo and Rate.

Let me know if it works: I'll be back in about six hours!
 
G

Guest

Neil,

I called tblPO Currency and have hence changed the relative names. However,
I still get an error message stating "Syntax error in from clause" and this
has been appearing quite alot.

My adjusted SQL now reads:

SELECT
Acceptance.[Request ID],
Acceptance.[Contract Quote Date],
Acceptance.[First Name],
Acceptance.Surname,
Acceptance.[Agent ID],
Acceptance.[Quote Sent],
PO.[Agent ID],
PO.CURRENCY,
PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID],
PO.[Serial ID] AS [PO_Serial ID],
Currency.ConvertTo,
Currency.Rate,
Currency.Rate*PO.PRICE AS Converted
FROM
Acceptance
((INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID])
INNER JOIN Currency ON Acceptance.CURRENCY = Currency.ConvertFrom)

I will be leaving work in an hour and back in the morning around 9 GST!
Matt

Neil Sunderland said:
Matt said:
SELECT Acceptance.[Request ID], Acceptance.[Contract Quote Date],
Acceptance.[First Name], Acceptance.Surname, Acceptance.[Agent ID],
Acceptance.[Quote Sent], PO.[Agent ID], PO.CURRENCY, PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID], PO.[Serial ID] AS
[PO_Serial ID]
FROM Acceptance INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID];
Click to expand...

I've only spent two minutes on this, so it might not work first time:

SELECT
Acceptance.[Request ID],
Acceptance.[Contract Quote Date],
Acceptance.[First Name],
Acceptance.Surname,
Acceptance.[Agent ID],
Acceptance.[Quote Sent],
PO.[Agent ID],
PO.CURRENCY,
PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID],
PO.[Serial ID] AS [PO_Serial ID],
tblPO.ConvertTo,
tblPO.Rate,
tblPO.Rate*PO.PRICE AS Converted
FROM
Acceptance
((INNER JOIN PO ON Acceptance.[Serial ID] = PO.[Serial ID])
INNER JOIN tblPO ON Acceptance.CURRENCY = tblPO.ConvertFrom)

This assumes that the currency conversions are in a table call tblPO
with fields ConvertFrom, ConvertTo and Rate.

Let me know if it works: I'll be back in about six hours!

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
N

Neil Sunderland

Matt said:
I called tblPO Currency and have hence changed the relative names. However,
I still get an error message stating "Syntax error in from clause" and this
has been appearing quite alot.
Click to expand...

My fault, I never did get the hang of nested joins!

See if this works instead:

SELECT
Acceptance.[Request ID],
Acceptance.[Contract Quote Date],
Acceptance.[First Name],
Acceptance.Surname,
Acceptance.[Agent ID],
Acceptance.[Quote Sent],
PO.[Agent ID],
PO.CURRENCY,
PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID],
PO.[Serial ID] AS [PO_Serial ID],
Currency.ConvertTo,
Currency.Rate,
Currency.Rate*PO.PRICE AS Converted
FROM
Acceptance
INNER JOIN (PO ON Acceptance.[Serial ID] = PO.[Serial ID]
INNER JOIN Currency ON Acceptance.CURRENCY = Currency.ConvertFrom)
 
G

Guest

Neil,

I got it working mate, cheers for that is brilliant
Sorry i didnt get back to you earlier but I have been in training all day

Many Thanks,
Matt

Neil Sunderland said:
Matt said:
I called tblPO Currency and have hence changed the relative names. However,
I still get an error message stating "Syntax error in from clause" and this
has been appearing quite alot.
Click to expand...

My fault, I never did get the hang of nested joins!

See if this works instead:

SELECT
Acceptance.[Request ID],
Acceptance.[Contract Quote Date],
Acceptance.[First Name],
Acceptance.Surname,
Acceptance.[Agent ID],
Acceptance.[Quote Sent],
PO.[Agent ID],
PO.CURRENCY,
PO.PRICE,
Acceptance.[Serial ID] AS [Acceptance_Serial ID],
PO.[Serial ID] AS [PO_Serial ID],
Currency.ConvertTo,
Currency.Rate,
Currency.Rate*PO.PRICE AS Converted
FROM
Acceptance
INNER JOIN (PO ON Acceptance.[Serial ID] = PO.[Serial ID]
INNER JOIN Currency ON Acceptance.CURRENCY = Currency.ConvertFrom)

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot determine casue of overflow error 1
Converting currency within a query 4
Calculation with data from different tables 3
Two tables, multiple relationships? 1
Update type conversion error with nulls 2
Query to divide. 4
Dlookup ? 4
dlookup problem 1

Top