counting ticks in reports

[rumplstilskin]

Hello everybody,


I have a problem at work. As Access absolute begginer I have created a database with all the products that we analyze in the lab. I have only one table and it's very large. I created a form as well, for filling that database and on that form I have 15 methods of analysis that can be ticked. I need to make a report (or query or anything else ) that would count how many times has each method been ticked. If posible, I would like to see how many times was it ticked form sample No. 500 to sample No. 600.
Is there a chance that someone can explain it step-by-step cause I'm not sure I can do it otherwise....

Thank you veeeeeeeery much!!!!!!

Nevena

[alansidman]

You will have to create an aggregate query. When you refer to Ticks, are you referring to checkboxes. If so, the values of a checked box is -1 while an unchecked box is 0. You will need to know this for your criteria.

Here is an explanation of aggregate queries. Scroll down to the group by section.

http://www.techonthenet.com/access/queries/index.php

[jzwp11]

Welcome to the forum!

Having just a single table points to a major flaw in your database design. If a sample/product can be analyzed using many methods of analysis, that describes a one-to-many relationship. Further, a method of analysis may be used on multiple samples which is another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (samples & methods), you have a many-to-many relationship. You will need the following 3 tables at a minimum.

tblSamples (or products whichever is applicable)
-pkSampleID primary key, autonumber
-txtSampleNo
-dteSample (sample date)
other fields related to the sample


tblMethodsOfAnalysis
-pkMethodID primary key, autonumber
-txtMethodNo
-txtMethodName

tblSampleMethods
-pkSampMethID primary key, autonumber
-fkSampleID foreign key to tblSamples (long integer number datatype)
-fkMethodID foreign key to tblMethodsOfAnalysis (lont integer number datatype)
-resultOfAnalysis