advice needed on adding checkboxes for a total

[ziggs]

I have written most of the easy ASP portion for a MS Access
DB insert. In the database, I have about 15 checkboxes in it. Five
of them represent a point total of 1 point, 5 of them at 2 points each
and 5 at 4 points each. Over time this database will get quite big.
In the database, I will most likely need a field that will represent
the point value of all 15 checkbox fields or I'll just have to use a
formula in all future reports, modify asp's and query asp's to
calculate the 15 checkboxes.

Anyway, can someone suggest which direction I should take (Total Point
Value field in the database or not). The problem that I worried about
with making a Total Point Value field is the formulas processing the
correct point total when the modify.asp changes some or all of the
checkbox values. The problem I have without the field is that future
yearly and monthly reports would be needed and I don't want the report
to be too slow since it will have to process at least 15 checkbox
fields for each record multiplied by tens of thousands of records in
the future.

Any thoughts? TIA

 

[Tarwn]

If you had those checkboxes in their own table and just linked to the original record by id's you could handle this with a fairly simple equation, ah well.

You should be able to build a fairly long set of if statements to calculate the total and return it in one field. Maybe something like(sorry, can't remember Access If notation):

((IF field1 = true THEN 1 ELSE 0) + (IF field2 = true THEN 1 ELSE 0 ) + ...etc ) as ttl_value

Now I'm thinking you should be able to embed that into an Access query which may or may not speed it up, dunno.

If you had them in seperate records in another table with their point score with them, it would be a simple matter of:

SELECT SUM(Table2.checkScore) as ttl_value FROM Table2 WHERE Table2.Table1_Key = whatever AND Table2.checkboxField = True

In any case, not sur if that helped or not,

-T
[/code]