counting occurrences (1 Viewer)

[hitchhiker]

I have created a database which is responsible for turning out several reports at my office. One of the tables in this database lists all products that have had a particular violation, what the violation was and the date of the violation. What this report needs to show is whether or not any given product had a violation during a particular month or not.

Can I write an iif statement that will return a '1' or a '0' based on whether there was a violation during a particular time period?

If so could you please provide an example of the syntax...

Thanks for the help.

 

[Rob.Mills]

Wouldn't it just be easier to list all the products that have a violation during the time period instead of listing all of them and giving a yes or a no?

 

[hitchhiker]

It most certainly would. But that's not how the boss wants it. They want a list of all projects under construction during the year, and then they want to see which of these projects have had any violations (ie. safety, etc) during each month of that year.

The ultimate goal is a percentage describing how succesfull we were at running violation-free jobs.

 

[Rob.Mills]

Bosses can really get annoying can't they?

Well then, create a field in your query:

IIF(isnull(Dlookup("[IDField]","[TableName]","[IDField]=" & [IDField] & " And DatePart("m",[DateField])=" & [MonthNumber]), "No","Yes")