Getting Error that the query exceeds 2 GB

[sportsguy]

This query just totals about 10% of the 3.2 million records. . .

Any suggestions??

SCB048M is a 631MB access2007 database. . .

any suggestions?? other than windows 7 x64 which i need and have at home.

SELECT PRODUCT_CODES.Market, Sum([Net_Revenue]*[US$Rate]) AS RevenueUSD
FROM (((((SCB048M INNER JOIN CUSTOMERMASTER_ACE ON SCB048M.CustomerNbr = CUSTOMERMASTER_ACE.ACCT_NBR) INNER JOIN SG_CRM_CUST_ACCOUNT_CLASS_CODES ON CUSTOMERMASTER_ACE.intAcct_Nbr = SG_CRM_CUST_ACCOUNT_CLASS_CODES.Account_Number) INNER JOIN CRM_ACCOUNT_CLASS_CODE_XREF ON (SG_CRM_CUST_ACCOUNT_CLASS_CODES.Class_Code = CRM_ACCOUNT_CLASS_CODE_XREF.Class_Code) AND (SG_CRM_CUST_ACCOUNT_CLASS_CODES.Classification_Account_Number = CRM_ACCOUNT_CLASS_CODE_XREF.Classification_Account_Number)) INNER JOIN PRODUCT_CODES ON SCB048M.Product = PRODUCT_CODES.ProdID) INNER JOIN DISTRICTS ON SCB048M.strDistrict = DISTRICTS.strDistrict) INNER JOIN FX ON DISTRICTS.Currency = FX.Currency
WHERE (((FX.Year)=2011) AND ((SG_CRM_CUST_ACCOUNT_CLASS_CODES.Default_Flag)="Y") AND ((SG_CRM_CUST_ACCOUNT_CLASS_CODES.Class_Code)="039") AND ((SCB048M.Fiscalperiod)>201100) AND ((SG_CRM_CUST_ACCOUNT_CLASS_CODES.Classification_Account_Number)="8461" Or (SG_CRM_CUST_ACCOUNT_CLASS_CODES.Classification_Account_Number)="8464" Or (SG_CRM_CUST_ACCOUNT_CLASS_CODES.Classification_Account_Number)="8462" Or (SG_CRM_CUST_ACCOUNT_CLASS_CODES.Classification_Account_Number)="8888" Or (SG_CRM_CUST_ACCOUNT_CLASS_CODES.Classification_Account_Number)="8889") AND ((CRM_ACCOUNT_CLASS_CODE_XREF.Accounts_Enabled_Flag)="Y")) OR (((FX.Year)=2011) AND ((SG_CRM_CUST_ACCOUNT_CLASS_CODES.Class_Code)="840") AND ((SCB048M.Fiscalperiod)>201100) AND ((CRM_ACCOUNT_CLASS_CODE_XREF.Accounts_Enabled_Flag)="Y") AND ((CRM_ACCOUNT_CLASS_CODE_XREF.CAN_Federal)=True))
GROUP BY PRODUCT_CODES.Market;

thanks

sportsguy

 

[Guus2005]

you are trying to join six or seven tables each with their own restrictions.
perhaps you can split your query up into smaller pieces.

for table fx you only need year=2011. so create a query qryFX2011 to use in the final query. do that with the rest of the tables. in the end you have less data to work with and perhaps errorfree.

let me know how that works out.

hth

 

[sportsguy]

FX table is about 10 rows, and i only use one row for every record. . .

The table runs within memory ranges with FX for other queries. . . not sure why its balking at this query. . .

 

[Guus2005]

and did you try the same with the other queries?
there are a lot of restrictions on the SG_CRM_CUST_ACCOUNT_CLASS_CODES table!