I am writing an SQL Pass-Through Query in Access 2002 to retrieve Data from an Oracle database. Everything works OK except my aggregate SUM function is not returning Decimal data -- looks like it is truncating the amounts. I've seen a thread in here (thread68-100697) that recommends reverting to Access 2000 but that's not a realistic solution for me (I don't control what application versions the company uses). It also gives some kind of workaround from Microsoft, but it appears to be a SQL Server function and I'm not familiar enough with the Oracle functions to adapt it. Can anyone help me get aggregated decimal data?
The SQL is as follows:
SELECT xx.PROJ_ID, SUM(xx.SALE_AMT) AS REVENUE
FROM PROJ_SALE_TABLE xx
WHERE xx.PROJ_ID='OBNFJ'
GROUP BY xx.PROJ_ID
SALE_AMT is stored in the database with 4 decimal places, but most of the data only uses 2 (dollars & cents). I've tried using a TO_NUMBER function, but that doesn't seem to help (perhaps I'm not taking advantage of all of the arguments). Many thanks for any help you can offer!
The SQL is as follows:
SELECT xx.PROJ_ID, SUM(xx.SALE_AMT) AS REVENUE
FROM PROJ_SALE_TABLE xx
WHERE xx.PROJ_ID='OBNFJ'
GROUP BY xx.PROJ_ID
SALE_AMT is stored in the database with 4 decimal places, but most of the data only uses 2 (dollars & cents). I've tried using a TO_NUMBER function, but that doesn't seem to help (perhaps I'm not taking advantage of all of the arguments). Many thanks for any help you can offer!
