Check for zero record sums, variable number of fields

[MePenguin]

I have a data input form for insect abundance counts per sample that resembles a crosstab:
[tt]
CODE SpeciesName S100 S204 S206
1.0210441 Trechus 0 1 2
1.0291045 Bembidion 0 0 0
1.051011 Pstrenuus 0 1 0
1.051012 Pdiligens 0 0 4
[/tt]

The form is generated on the fly after users enter sample details (s100, s204...), and choose species (CODE=primary key). The user then enters abundance data (counts) in an Excel like fasion. When done, they click a button and the data is parsed into normalised tables.

I need to be able to check for empty samples and empty species (zero sums?).

I can handle summing columns by looping the fields collection and constructing SQL in VBA:

SELECT DISTINCTROW Sum(tblCountsheetForEdits.s100) AS [Sum Of s100] etc...

(missing out the CODE & species name fields) then checking for zeros.

...and I could VBA a couple of nested loops to sum the rows and check for zeros - BUT can I do it more quickly and tidily with queries whilst retaining the flexibility?

Fieldnames will vary, as will the number of fields.

Thanks,

Phil

---------------
Pass me the ether.

 

[danvlas]

How about having a 'total' field on the input form to sum up all the fields?

Then, when you parse the data, if the field is 0 or there is no 'code', just ignore the row and move to the next one?


HTH





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[MePenguin]

Good idea - funny thing is I have a hidden field on the input form called totals (as the result of reusing a crosstab query). I suppose I could convert it into a calculated field and do as you say.

Thanks!

I'll give you a star if it works ;-)

Phil

---------------
Pass me the ether.