I'm fairly unfamiliar with Access but am an avid Excel user. I had to recently switch a function I'm doing to Access due to the record size. The issue I'm having is the queries I've set up are running painfully slow, as in hours. I'm not sure what I'm doing wrong. I have 3 tables with records of about 2 million, 7 million and 55K respectively. My first question is, is it the size that is my issue?
I'm querying the 3 tables based on 2 matching criteria (contract ID and sku) and bringing in all fields from table 1 and 1 field each from tables 2 and 3. I'm then summing the 1 field from each of the 3 tables. Can someone help me speed this up? I'm indexing fiscal month, fiscal year, contract ID and sku. Any assistance would be greatly appreciated! I've tried deleting unnecessary columns from my tables, eliminating multiple queries, etc, but nothing seems to help.
SELECT [Rpt 1 Rev].[Fiscal Month ID], [Rpt 1 Rev].[Fiscal Year ID], [Rpt 1 Rev].[Contract ID], Sku_Matrix.Term, Sum([Rpt 1 Rev].[DRBA]) AS [SumOfDRBA], Sum([Rpt 2 Rev].[ERA]) AS [SumOfERA], Sum([Rpt 3 Rev].[DRBA]) AS [SumOfDRBA], Sum(nz([Rpt 1 Rev]![DRBA],0)-nz([Rpt 2 Rev]![ERA],0)-nz([Rpt 3 Rev]![DRBA],0)) AS Net INTO [Net_Detail_Rev Table]
FROM (([Rpt 1 Rev] INNER JOIN Sku_Matrix ON [Rpt 1 Rev].Sku = Sku_Matrix.Sku) LEFT JOIN [Rpt 2 Rev] ON ([Rpt 1 Rev].Sku = [Rpt 2 Rev].Sku) AND ([Rpt 1 Rev].[Contract ID] = [Rpt 2 Rev].[Contract ID])) LEFT JOIN [Rpt 3 Rev] ON ([Rpt 1 Rev].Sku = [Rpt 3 Rev].Sku) AND ([Rpt 1 Rev].[Contract ID] = [Rpt 3 Rev].[Contract ID])
GROUP BY [Rpt 1 Rev].[Fiscal Month ID], [Rpt 1 Rev].[Fiscal Year ID], [Rpt 1 Rev].[Contract ID], Sku_Matrix.Term;