Calculate USD for specific Company

[crystalfun]

I have a table where 5 companies have costs in USD and one company has a cost in Canadian dollars.

Company 1-5 = USD
Company 6 = CAD

I want to write a query where the person running it can enter a conversion rate that can convert the CAD into USD thereby showing costs of all records (Co's 1-6) USD in all 3 $$ fields. How can I do this?

Field1 - Company
Field2 - Item Description
Field3 - $Cost
Field4 - $Accum Depr
Field5 - $Net Book Value

 

[scriverb]

Give this SQL a try in a new query:

Select A.[Company], A.[Item Description], IIF(A.[Company]<6,A.[$Cost],A.[$Cost]*[Enter Conversion Rate: ]), IIF(A.[Company]<6,A.[$Accum Depr],A.[$Accum Depr]*[Enter Conversion Rate: ]), IIF(A.[Company]<6,A.[$Net Book Value],A.[$Net Book Value]*[Enter Conversion Rate: ]) Order By A.[Company];

Post back with questions or comments.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[bhoran]

yOU COULD ALSO DO A QUERY for company 6 and multiply each column by the exchange rate.

Then do a separate query rolling up the USD companies and then a union query over those 2 queries to give you an amalgamation of all the USD figures.

 

[SkipVought]

Hi,

You are missing an important piece of data in your database...

the Country or

the CurrencyUnitOfMeasure



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[scriverb]

SkipVought makes a good point. If you had a table with a Country designation, unit of measure, and conversion rate we could link through a Country field and wouldn't have to prompt for the rate. If you had numerous countries and numerous rates the expression would just use the fields linked to your records.

Just a thought if this is going to grow on you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SkipVought]

Actually, you ought to have a COMPANY table with data relating to the company, including country related data.

Then you ought to have an INVOICE table with data relating to the invoice, like Invoice Number and Invoice Date and Company Invoiced.

Then you ought to have an INVOICE_DETAIL table with each invoice line item data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884