I have an aggregate query in Access 2010 that is returning values for a summed field that are extremely higher than what you see when look at the raw data with Excel.
When I was setting up the query in the query designer I noticed that, before turning it into an aggregate query, the records were appearing multiple times in the result; so I added SELECT DISTINCT to get around this. Then I added the aggregate SUM and now it doesn't work correctly.
At first I thought it was somehow ignoring the SELECT DISTINCT, but when made a copy of the query and removed the aggregate and then ran the query and popped it out into Excel and put subtotals on it the values returned in the aggregate query are not exact multiples of the subtotals from Excel. In fact, when I divided the aggregate group by summed value by the respective subtotal from Excel none of the multipliers are the same.
SELECT DISTINCT vd_SalesRep.[SalesRep Name] AS Rep,
Sum(vf_SALESDETAIL.[SalesTxn Document Subtotal Amount]) AS [Total Quotes Amount]
FROM ((vf_SALESDETAIL INNER JOIN vd_Company ON vf_SALESDETAIL.LinkToCompanyID = vd_Company.LinkForCompanyID)
INNER JOIN vd_SalesRep ON vf_SALESDETAIL.LinkToSalesRepID = vd_SalesRep.LinkForSalesRepID) INNER JOIN vd_CalYrTxnDate ON vf_SALESDETAIL.LinkToTxnDateID = vd_CalYrTxnDate.LinkForTxnDateID
WHERE (((vd_Company.[Company Name])="ABC Company, Inc.") AND ((vd_CalYrTxnDate.[CalYr Txn Date]) Between [Forms]![frmLaunchpad]![StartDate] And [Forms]![frmLaunchpad]![EndDate])
AND ((vf_SALESDETAIL.[SalesTxn Document Type])="Estimate"))
GROUP BY vd_SalesRep.[SalesRep Name]
ORDER BY vd_SalesRep.[SalesRep Name];