Excel formula to access calculation conversion help

[Scott Calkins via AccessMonster.com]

Currently I am using Excel to gather information from a large csv file.
The problem is it is getting way too large for excel to handle (projected
to 1.9 million lines.) Due to this I am working on using Acess to get the
info I need.

I need to get total counts of entries where field A="x", field b="y", field
c =<z. As a example, one of the formulas in excel that I use is the
following:
{=COUNT(IF(('Raw data (2)'!L1:L65535="S")*('Raw data (2)'!B1:B65535="L")*
('Raw data (2)'!O1:O65535=4),))}

Could someone point me in the right direction on a formula or calculation
for access to do this?

 

[John Nurick]

Hi Scott,

Assuming you have imported or linked the csv file to an Access table,
you do this sort of thing with a "totals" query or a data aggregate
function:

For instance, to count records where
A="x", field b="y", field c =<z
you'd use a query like this (create a new query, don't select any
tables, and switch to SQL view and type this in, adjusting for your
actual names. Then switch to Query design view to see how it's
represented in the design grid.

SELECT COUNT(A) AS NumberOfRecords
FROM MyTable
WHERE (A="x")
AND (B="y")
AND (C<=z)
;

Check out Parameter Queries in help to see how to get the x, y and z
from the user or a fields on a form rather than hard-coding them into
the query. More complex (*much* more complex) queries are possible, e.g.
using the GROUP BY clause to return a series of subtotals.

Using DCount, you could do something like this:

Dim strWhere As String
Dim lngNumRecs As Long

strWhere = "WHERE (A="x") AND (B="y") AND (C<=z)"
lngNumRecs = DCount("A", "MyTable", strWhere)