Counting yes/no fields with differrent names

[fgwiii]

Hello,

I have a database that contains the following fields, some of which (all
fields starting with Page_) are selected via check marks;
ID, Date_Received, Site_Number, Subject_Number, Subject_Initials,
Page_1, Page_2, Page_3, Page_4, Page_5, Page_6, Page_7, Page_8, Page_9, Page_10_A,
Page_10_B, Page_10_C, Page_10_D, Page_10_E, Page_11_A, Page_11_B, Page_11_C,
Page_11_D, Page_11_E, Page_12, Page_13_A, Page_13_B, Page_13_C, Page_13_D,
Page_13_E

My intentions are to provide a total page count for each subject. My
problem is that I am not really sure on how to accomplish this.

Any thoughts are appreciated.

Thanks,

Fred

 

[Douglas J. Steele]

No offense, but your table is inappropriately designed.

Having fields with names like Page_1, Page_2, Page_3, Page_4 etc is known as
a repeating group, and is definitely not recommended. Rather than having a
separate field for each, you should have a second table, where each check
box is a separate row (along with a field that indicates what the check box
represents). For some good resources on database normalization, see what
Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

That having been said, while you're working on redesigning your tables, you
can use a query along the lines of:

SELECT ID, Date_Received, Site_Number, Subject_Number, Subject_Initials,
Sum(IIf(Page_1, 1, 0)) AS Total_Page _1, Sum(IIf(Page_2, 1, 0)) AS
Total_Page_2, Sum(IIf(Page_3, 1, 0)) AS Total_Page_3, ...
FROM MyTable
GROUP BY ID, Date_Received, Site_Number, Subject_Number, Subject_Initials

 

[fgwiii]

Will the design and syntax you propose allow me to provide a total number of
pages received per subject #? See example below.
=========================
Site: 1701
Subject :10103
Pages received: 14
=========================
Site: 1701
Subject :10123
Pages received: 19
=========================
Site: 1734
Subject :10001
Pages received: 10
=========================


thank you,

Fred

 

[Douglas J. Steele]

Sounds as though you want a total number of pages, not a count of yes/no
fields.

If you structure the tables correctly, that will be very easy. Since the
second table will have one row for each page, you'd simply need to count the
number of rows associated with each subject.

SELECT MyTable.Site_Number, MyTable.Subject_Number, Count(*) AS Total_Pages
FROM MyTable INNER JOIN MyOtherTable
ON MyTable.Site_Number = MyOtherTable.Site_Number
AND MyTable_Subject_Number = MyOtherTable.Subject_Number
GROUP BY MyTable.Site_Number, MyTable.Subject_Number