[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Total / Avg ?

Posted on 2009-12-30
6
Medium Priority
?
238 Views
Last Modified: 2012-05-08
In Access in the table (an ODBC linked table) the Quantity shows 15.764 and there are 5 records. When I change the Query to Total and sum on Quantity it gives me a total of 79.564, and an Avg of 15.9128. But it should be Total: 78.82 and Avg: 15.764
0
Comment
Question by:Jess31
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 26148248
Did you try like this?

SELECT SUM(Quantity) AS SUM_Quantity, AVG(Quantity) AS AVG_Quantity FROM YourTable
0
 
LVL 1

Author Comment

by:Jess31
ID: 26148339
the problem seems to be that the query manager is not displaying the numbers correctly. It repeats the Quantity of the first row when in face the numbers are different. When I export it from Access to SQL Server I see the correct numbers. (This is not a native access table, only linked)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26149223
There are probably residuals beyond the third decimal place that are taken into consideration although you only see the three decimal places.  As an example an Access table with a double field which  you then change to currency.  Although you only 'see' four decimal places, the other stuff is still there.  
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:Jess31
ID: 26151427
no. any residuals would not have this effect. There seems to clearly be a problem in Access 2K Query. As pointed out when I copy this data to SQL sever the data past the decimal are diferent even to the left of the third position.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 26155798
Five records each with a value of 15.764 - right?  Try copying the five records to a new table, link to the new table, and see what your Sum() and Avg() results are.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26350482
Thanks, glad to help.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question