Problems with summing up info in a report

[clickster]

Hello,

Here is the problem. I have a table that contains information on the software installed on each of my computers. The table has a "Computer Name" field and about 30 other fields (one for each type of software that might be installed). In each of the software fields, I have a Yes/No checkbox that links to a form I use to edit the data. I am trying to determine the total number of installed copies of each piece of software, so I need a report that will go through the "Win 95" field, add up all of the boxes that are checked and give me the total in a report. I need it to do this for all of the software fields (everything except "Computer Name", which has no bearing on the report). I would even settle for finding a way of summing up the info and putting the results into another table (I could then easily make a basic report from that table with the summed up info). Any help would be greatly appreciated. Thank you

 

[JerryDennison]

1) You really should consider normalizing your data. As it is, you'll have to add a field if you add new software. With a normalized data base you would not have to do that, nor would you be using near as much space.

2) To work with your existing structure is create a totals query. Add all the Yes/No fields to the QBE grid, press the Sum button and change all the total properties to Count. Add one of the Yes/No fields to the grid a second time (in the first column position) and leave it's total property as Group By, then set it's criteria to Yes. Run the query and you'll get a count of all copies for each type. (ps, uncheck the first column's view checkbox as it's only being used for grouping and filtering).