Sum with decimal's way off course

[Ace71425]

Hello...I have a couple querys that do some math and then expound upon that math...basically here's the point im stuck at

Everything works GREAT up to the last query, I have a column with decimals

.09
.04
.02

Now when you add these up say on a calculator you get .15...when I do this in a sum column of a query it's WAY off base... like the example above would be something like .67 which is like what the hell...I've tried messing with the formatting and with the formatting of the original fields that the queries are doing the math on changing them to fixed and double and single and everything you can think of...any ideas?

 

[sheetspread]

Are there rows filtered in the query that the sum might be picking up? Also be sure the decimals are showing on all relevant tables/queries (with standard, double, etc.) otherwise they may be chopped off in the sum.

 

[Ace71425]

What do you mean?

 

[sheetspread]

How many tables are in the query? Is there a filter condition in design view (i.e. WHERE clause in the SQL)?

 

[Joe4]

Can you post the SQL code of your query that returns the three values you have shown, and then post the SQL code of your SUM query that is returning odd results?

 

[Ace71425]

This is the first one...Expr3 is what im trying to sum up

SELECT TaskTotals_Time.Rep, TaskTotals_Time.Action_Taken, TaskTotals_Time.Datestamp, TaskTotals_Time.CountOfAction_Taken, TaskTotals_Time.Times, TaskTotals_Time.Hours_Working, [TaskTotals_Time]![Expr1]/[TaskTotals_Time]![Expr2] AS Expr3
FROM TaskTotals_Time
GROUP BY TaskTotals_Time.Rep, TaskTotals_Time.Action_Taken, TaskTotals_Time.Datestamp, TaskTotals_Time.CountOfAction_Taken, TaskTotals_Time.Times, TaskTotals_Time.Hours_Working, [TaskTotals_Time]![Expr1]/[TaskTotals_Time]![Expr2];

This is the second one that sums up expression 3 all screwy

SELECT TaskTotals_Comb.Rep, TaskTotals_Comb.Datestamp, Sum(TaskTotals_Comb.Expr3) AS SumOfExpr3, [PercAverage_Comb]![Timedifference]/[TaskTotals_Time]![Expr2] AS Should_Be
FROM (TaskTotals_Comb INNER JOIN PercAverage_Comb ON (TaskTotals_Comb.Datestamp = PercAverage_Comb.Datestamp) AND (TaskTotals_Comb.Rep = PercAverage_Comb.Rep)) INNER JOIN TaskTotals_Time ON (PercAverage_Comb.Datestamp = TaskTotals_Time.Datestamp) AND (TaskTotals_Comb.Datestamp = TaskTotals_Time.Datestamp) AND (TaskTotals_Comb.Rep = TaskTotals_Time.Rep) AND (PercAverage_Comb.Rep = TaskTotals_Time.Rep)
GROUP BY TaskTotals_Comb.Rep, TaskTotals_Comb.Datestamp, [PercAverage_Comb]![Timedifference]/[TaskTotals_Time]![Expr2];

 

[sheetspread]

INNER JOIN will only pull records that match by a given field value, so be sure all the components of the total you're looking at are actually in the query. Also be sure the decimals are showing up in the query and all table/query sources (re-check, this is a common oversight).

 

[Joe4]

Well, you are introducing new links in your second query that aren't in your first query, so it really isn't an "apples to apples" comparison. There are other factors at work there, and without seeing your data tables, it is hard to speculate what might be going on.

However, if your first query is returning the exact number of records you want to sum up, why not just create a new query off of that one to do your sum?
For example, let's say we named that query "Query1" and you want to Sum up Expr3. Then just use:

SELECT SUM([Expr3])
FROM Query1;

 

[Micron]

Well, you are introducing new links in your second query that aren't in your first query

Not sure why you conclude that unless you're referring to TaskTotals_Comb. That's probably the name of the first query - I don't see where that name was supplied, so it's just a guess.

Ace, what I'd do is create a copy of the first query and in it, remove any aggregate functions (count/total) but leave everthing else. Then run it and examine the records and look for duplicates. You have several joins between pairs of tables, and I suspect that is causing duplicate records that you will sum or count, but will not see with your present query. If you have duplicates, you might have to eliminate some of those joins - they may not be required, especially if one of them is a primary key.

First, I'd try setting the query properties to Unique Rows and Unique Values (they are mutually exclusive so you can only choose one or the other at a time). If that doesn't eliminate duplicate records, play with the joins. As a last resort, start a query with one table and build on that until you discover what causes the duplicate records.

If there are no duplicate records, then I have no other suggestion at this time.

 

[Joe4]

Well, you are introducing new links in your second query that aren't in your first query

Not sure why you conclude that unless you're referring to TaskTotals_Comb. That's probably the name of the first query

I just looked at the query, it is pretty obvious.

The first query only has one data source: TaskTotals_Time
The second has multiple (3) data sources: TaskTotals_Comb, PercAverage_Comb, TaskTotals_Time

Any time you add data sources, if your joins are not done properly of you have any one-to-many or many-to-many links, you could be creating duplicates.

I think we are really talking about the same thing here. Perhaps that might have been clearer if I had chosen to use the word "joins" instead of "links". I sometimes use those words interchangeably, those "joins" is probably the better choice, and it wouldn't be confused with things like linked tables or hyperlinks, etc.