Converting Excel Function to Access Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Access has domain function DSum and DCount that, depending on how your
data's organized, will probably do what you need.
 
Back
Top