Count in query

[DaveN]

My database has two tables. The first is a lookup table for anomalies (wrong
date, wrong time, wrong location, etc). It grows as more anomaly catagories
come up. The second consist of fields: Event, Date, Time, anomaly1, anomaly2,
anomaly3 and anomaly 4. The different anomalies in the Event table can be any
of the lookup anomalies in any order. I currently have a query that looks up
the anomaly from the lookup and lists the date and times it occured, using
the OR function.

My problem is now I need to count the number of times an anomaly comes up
and the percentage of that anomaly to the total number of anomalies for a
given time period. The time period can be set up for criteria of start date,
end date, but how do I get the query to count it and give the percentage so I
can sort it in a report?

 

[KARL DEWEY]

You should change your table structure to like this --
Event
DateTime --- dafault Now()
Anomaly

Create a multi-field primary key of the field above. When you enter your
first Anomaly copy the DateTime to the next record if you have more than one.

Using this table structure you can count the number of occurences for each
Anomaly.