Calculations in Access and Excel don't match

[S Sylvester]

I am using Access to pull information from our internal AS400 (historical
data that does not change). This link to the AS400 is the table I am basing
my initial query on. When I go through all the queries the totals do not add
correctly. I view the data in a report and have the report sum the field.
When I manually (or export to Excel) add the numbers shown in the report they
add up to more than what Access is totaling in the report.

I am using Access to create an "single button" report to show the linear
regression of our lines per tote as it relates to our picking rate. I want
to sample our AS400 daily to continually increase the number of data points I
am using in the study. The final report will calculate the daily production
expectation based on previous performances and variances in the work through
the formulas.

Here is what I am doing....
Query 1 = data from linked table (Date range, Aisle, Lines Picked, Totes
Picked)
Query 2 = built from Query 1 ( group by Aisle, Date and sum Lines and sum
totes)
Query 3 = built from Query 2 (group by Aisle, calculate Lines Per Tote (sum
lines / sum totes))
Query 4 = built from Query 3 (group by Aisle, sum Lines Per Tote

For the 21 data points in December, Access calculated the total to be 353,
the actual total (using the numbers produced by Access is 363). When I
increse the number of data points the variance increases as well

All the quieries I am using in this study have the same problem, the same
exact variances.

Two questions... why is there a difference in the numbers and how do I fix
the problem.

 

[christine caroen]

I can think of one thing I've encountered where Access counts blank cells,
that I perceived as being empty as "1". When I've imported data from excel
into access, I have had to clear content and/or delete the apparantly already
blank cells. Then the counts added up correctly.
Can you pinpoint when your first total goes wrong, is it in query 2 already
or is later?
Not sure this will help you.
Also, I just import the excel tables instead of linking so that I can do
this cumbersome but necessary data cleaning.

 

[S Sylvester]

The totals are not wrong until the last query, when the query is summing the
calculation from the previous query.