Totals on Union Query and Crosstab Query Do not match

[jrg24]

I ran a union query to compile a list of costs. I then ran a crosstab query on this union query. I used the "Totals" function in Access to check the totals of both queries to see if they match. The do not. The union query total is less than the crosstab but some how the crosstab query result matches what I know to be on the source data. The union query converts all amounts to currency. Does anyone know why this would be?

[rpeare]

I seriously doubt the conversion to currency is the issue. If you're using ccur(<value>) you shouldn't have an issue, if you're using format(<value>, "Currency") you should still be fine though it may be treating the value like a string after the conversion (I haven't done any real testing to see if it's similar to format(<value>, "Short Date") where the <value> would be treated like a string after the conversion rather than a date).

Perhaps post your SQL statements or create a sample database that has enough fake information in it to re-create the problem.

[jrg24]

I seriously doubt the conversion to currency is the issue. If you're using ccur(<value>) you shouldn't have an issue, if you're using format(<value>, "Currency") you should still be fine though it may be treating the value like a string after the conversion (I haven't done any real testing to see if it's similar to format(<value>, "Short Date") where the <value> would be treated like a string after the conversion rather than a date).

Perhaps post your SQL statements or create a sample database that has enough fake information in it to re-create the problem.

I appreciate the reply. I have redesigned my queries and the problem does not seen to be an issue any more. All values were converted via CCur.