I have a local table in Access that I use as a mapping table for quarter and fiscal year. The local mapping table has two fields: quarter and fiscal year. The quarter field is set as a primary key so there are no duplicates.

The other table that is housed on our SQL Server is our bookings table and it has many fields including quarter, customer, revenue. There's approximately 3 milliion records.

I created a group query that joins these two tables together by quarter (1 to many) to sum up the revenue for each customer, fiscal year.

The problem is when I join the local Access table to the linked SQL table in Access, the summing of the revenue increases dramatically. It's not an exact ratio though. I've even deleted all the quarters except one in the local mapping table but it still increases the actual revenue for that quarter.

Now the strange thing is, if I bring the mapping table into SQL Server, it will work fine using the same query. It will also work if I bring the SQL Server table into access (subset) as a local table.

I would like to be able to use local tables with SQL Server tables if possible.

Below is the query I used in Access.

SELECT [dbo_bookings_current-SQL].quarter, [dbo_bookings_current-SQL].area, [dbo_bookings_current-SQL].region, [dbo_bookings_current-SQL].parent_name, [dbo_bookings_current-SQL].parent_dun, [dbo_bookings_current-SQL].customer_name, [dbo_bookings_current-SQL].line_of_business, [dbo_bookings_current-SQL].organization, [dbo_bookings_current-SQL].comp_eligible, [dbo_bookings_current-SQL].revenue_business_type, [dbo_bookings_current-SQL].solution, [dbo_bookings_current-SQL].licensee_state, [dbo_bookings_current-SQL].licensee_postal_code, Sum([dbo_bookings_current-SQL].usd_revenue) AS SumOfusd_revenue, Sum([dbo_bookings_current-SQL].dollars_wo_renew) AS SumOfdollars_wo_renew, [Qtr to Fiscal Year Map].[Fiscal Year] INTO [FY07-Historic Tablea]
FROM [Qtr to Fiscal Year Map] INNER JOIN [dbo_bookings_current-SQL] ON [Qtr to Fiscal Year Map].Quarter = [dbo_bookings_current-SQL].quarter
GROUP BY [dbo_bookings_current-SQL].quarter, [dbo_bookings_current-SQL].area, [dbo_bookings_current-SQL].region, [dbo_bookings_current-SQL].parent_name, [dbo_bookings_current-SQL].parent_dun, [dbo_bookings_current-SQL].customer_name, [dbo_bookings_current-SQL].line_of_business, [dbo_bookings_current-SQL].organization, [dbo_bookings_current-SQL].comp_eligible, [dbo_bookings_current-SQL].revenue_business_type, [dbo_bookings_current-SQL].solution, [dbo_bookings_current-SQL].licensee_state, [dbo_bookings_current-SQL].licensee_postal_code, [Qtr to Fiscal Year Map].[Fiscal Year]
HAVING ((([dbo_bookings_current-SQL].customer_name)="AMERICA ONLINE INC") AND (([dbo_bookings_current-SQL].organization)="FIELD") AND (([dbo_bookings_current-SQL].comp_eligible)="QUOTA"));