Converting Excel Function to Access Query

[Guest]

I'm looking to replicate functionality I created in Excel to an Access
Database. The conversion is necessary because of the row limit within Excel
and the difficulties inherent in that limitation.

The concept is the following:

I have incidents that occur each month, some every month, some sporadically.
I sum these incidents for annual totals, and then calculate incidents/month,
reflecting the true incidents/month -- not just dividing by 12, but counting
the number of months in which the incident actually occurred.

Variables: Incident (any text), Date (Month), Totals (per month)

The calculation looks like this:

# Incidents = SUMIF(Range,Incident,Totals) * Incidents/year

Months = COUNTIF(Range,Incident) *Incident is only listed once/month if
present

Incidents/Month = # Incidents/Months

My tables replicate all of the raw data in excel, and I use queries to match
the Excel output, except for the Months calculation and Count/Month (which is
easy once you have Months).

Any guidance is greatly appreciated.

 

[Douglas J. Steele]

Access has domain function DSum and DCount that, depending on how your
data's organized, will probably do what you need.