AnsweredAssumed Answered

Need help with record summary!

Question asked by JohnDeere on Mar 13, 2011
Latest reply on Mar 18, 2011 by philmodjunk


Need help with record summary!


I have a database that contains chemical records.  There are five fields in each record that contain chemical names.  Each field has a unique name.   Each of these fields are populated by the same pop-up value list, which contains about 20 chemical names. Each record is a different combination of these same chemicals.  Each record may have 1-5 of these fields filled.  Each chemical field also has a Qty. field associated with it, also each with a unique field name.

Heres my problem.  I want to be able to get a summary of the total qty of each chemical used for a group of records.  They will also be grouped by other criteria in each record.  (region, state, etc)  The problem is that each chemical could be in any of the 5 chemical fields, and in different combinations depending on the order the user entered them.  How do I get the total qty of chemical "A", when it can appear in any of the 5 fields in each record??

I would really appreciate some help!

thanks in advance.