Adding a currency conversion option into a Query

[Brainfade]

We have a query that calculates the cost total of three different types of costs between three different parameters. I now need to run a query that prompts the user for the days currency conversion rate to create a new report. I don't want to permanently convert the original fields, it is purely a monthly report where when the report is run is shows both British £ and USD. I get the feeling that it should be simple but I can't see it at present and have no other query that runs this kind of report to crib from. I've been TOLD this should be fun!

 

[pr2-eugin]

Show the SQL of the Query you got right now. Or atleast a pseudo code of where you want this conversion thing put in.

 

[Brainfade]

Here is the query in SQL format
SELECT DISTINCTROW [NON-CONFORMITY MONTHLY COST BASE].[COST TYPE], Sum([NON-CONFORMITY MONTHLY COST BASE].[LABOUR COST]) AS [Sum Of LABOUR COST], Sum([NON-CONFORMITY MONTHLY COST BASE].[OVERHEAD COST]) AS [Sum Of OVERHEAD COST], Sum([NON-CONFORMITY MONTHLY COST BASE].[MATERIAL COST]) AS [Sum Of MATERIAL COST]
FROM [NON-CONFORMITY MONTHLY COST BASE]
GROUP BY [NON-CONFORMITY MONTHLY COST BASE].[COST TYPE];

 

[JHB]

I would create a second query based on the first query, and use parameters to get the days currency conversion rate for $ and £.