TRANSLATE EXCEL CODE TO ACCESS CALCULATION

[stevembe]

I am very much a beginner on Access and am trying to migrate an Excel workbook into an Access Database as it is far more suitable. However, one piece I am stuck on is replicating the following formula in Access format:

=SUMPRODUCT(('Source Data'!$L$2:$L$500="PERFORMANCE")*('Source Data'!$E$2:$E$500="I")*('Source Data'!$I$2:$I$500<>"Subcontractor"))

How can I do a calculated field to count 3 sets of data over the 3 column headings?

Any help greatly appreciated.

[Bob Phillips]

I assume you will run a query? What are the tables and column names?

[stevembe]

I assume you will run a query? What are the tables and column names?

Hi, it is one table called Monthly Extract and the columns are

Branch
D/I
Position Name

[Bob Phillips]

Something like


SELECT Count(Branch) AS [Count]
FROM [Monthly Extract]
WHERE ((([Monthly Extract].[Branch])="PERFORMANCE") AND (([Monthly Extract].[D/I])="I") AND (([Monthly Extract].[PositionName])<>"SubContractor"));

[stevembe]

Something like


SELECT Count(Branch) AS [Count]
FROM [Monthly Extract]
WHERE ((([Monthly Extract].[Branch])="PERFORMANCE") AND (([Monthly Extract].[D/I])="I") AND (([Monthly Extract].[PositionName])<>"SubContractor"));

Sorry, I am a beginner, where do I put that code?

[mancubus]

it's a query.
Create -> Querius -> Query Design
close Show Table dialog.
click SQL button in the bottom right corner.
paste the query here.
then save as query (with a meaningful name).

[Bob Phillips]

I am surprised that you want to do this in Access but don't know what a query is?

[stevembe]

Forgive my ignorance, as I said I am a beginner but I do know what a query is but not so good on writing SQL. When I put that query in and I click Run it just asks me for Parameter Values?

[Bob Phillips]

That suggests that your table and/or column names are not exactly as given, the table and query do not match up.