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.