Add an Excel formula to an access query? Questions about a project.

[Alane]

Hello,

I have a large amount of data in an excel spreadsheet (over 22K in rows). I imported the spreadsheet into access as a table and made some queries, but the problem is that I'm not sure how to incorporate the much needed excel formulas into the queries to analyze the table. Here is one of the queries (SQL view):

SELECT DISTINCTROW [Historical 2011 to June 2013].EffectiveYear, [Historical 2011 to June 2013].LifeEventCode, [Historical 2011 to June 2013].AccCarrier, Avg([Historical 2011 to June 2013].PayRate) AS [Avg PayRate], Sum([Historical 2011 to June 2013].AccCost) AS [Sum Of AccCost]
FROM [Historical 2011 to June 2013]
GROUP BY [Historical 2011 to June 2013].EffectiveYear, [Historical 2011 to June 2013].LifeEventCode, [Historical 2011 to June 2013].AccCarrier
HAVING (((Sum([Historical 2011 to June 2013].AccCost))>0));

and here is the excel formula I need to add in here somehow: =COUNTA(client name OE'!J2:J2418)-SUMPRODUCT(--('client name OE'!J2:J2418={"DECLINED","NOTOFFERED","INELIGIBLE","MAINTAIN",""""})).

This excel formula is excluding cells that contain "declined, notoffered, ineligible, maintain, and any blank cells. When I run the query on the table, I also want it to be able to know NOT to count those cells.

Does anyone know how to do this?

Thanks!

 

[Fazza]

welcome, Alane

between the FROM clause and the GROUP BY clause, try adding

WHERE [Historical 2011 to June 2013].your_field_name NOT IN ('DECLINED', 'NOTOFFERED', 'INELIGIBLE', 'MAINTAIN') AND [Historical 2011 to June 2013].your_field_name IS NOT NULL

regards

 

[Alane]

Thank you Fazza! You're awesome!

 

[Alane]

This what I ended up with for the WHERE statement to work: WHERE (Acc <> 'DECLINED' and Acc <> 'NOTOFFERED' and Acc <> 'INELIGIBLE' and Acc <> 'MAINTAIN' and Acc <> '').