Calculation with data from different tables

[Gerry]

Hello

I have 2 tables with each 2 fields, one of them is common. This common
field lets call it 'Currency' contains values as AUD, USD, JPY, EUR.
In case 'Currency' matches for both fields I would like to multiply
the corresponding value for that currency by the corresponding value
for it in the other table.
Can anybody help me to write the code?
Thx
Gerry

 

[Sharon V]

This is driving me crazy - my unmatched query wizard will not display
field names for any tables I choose. All my other query wizards work
properly. I am running Access 2003. I have tried using both 2003 and
2000 formats. It's not my access databases because the wizard works
correctly over the network using other people's Access software. It is
with every Access mdb database I have tried. I have checked every
setting imaginable but don't see anything I can do to make this work.
It is driving me crazy! (And, yes, I do know how to do this manually,
but now I'm on a mission to get it working!).

Thanks in advance,
Sharon

shajack2000 at yahoo.com

 

[Marshall Barton]

I have 2 tables with each 2 fields, one of them is common. This common
field lets call it 'Currency' contains values as AUD, USD, JPY, EUR.
In case 'Currency' matches for both fields I would like to multiply
the corresponding value for that currency by the corresponding value
for it in the other table.
Can anybody help me to write the code?

This kind of thing should be done using a query something
like:

SELECT table1.amount, table1.Currency,
table2.convertrate,
table1.amount * Nz(table2.convertrate, 1) As NewAmt
FROM table1 LEFT JOIN table2
ON table1.Currency = table2.Currency

 

[Sharon V]

Replying to my own post - no one here has ever encountered this???