JGalletta
Windows 7 Access 2010
- Local time
- Today, 03:00
- Joined
- Feb 9, 2012
- Messages
- 149
I have a report which details the cost and amount of chemicals being applied to a farm to combat target pests. This report is grouped by [Target Pest].Value which ends up displaying the report as a groups of records grouped on individual values from [Target Pest]. This is the expected and desired result.
However, sometimes we record applications of chemicals which target multiple pests. When this occurs, grouping remains the same, but records containing multiple target pests will be present in the groups for each of the pests targeted. This is also expected and desired, but with an unwanted consequence. This duplication is a good, realistic and truthful representation of chemical applications, but in terms of representing the cost to spray for each pest, the duplication of costs is false and misleading representation.
My attempt to solve this problem was to divide the cost associated with each record by the amount of pests each record targets (on a per record basis in a text box control). I.e. a record of an application which targets 3 pests would show 1/3 of the total cost of that application as it's cost in this report. The record would show up three times (once in each pest's group), and the total sum of costs would remain accurate.
One problem I run into when performing this calculation is that I cannot make a direct reference to the number of target pests of a specific chemical application because they are entered in a multivalued field. One way of extracting this count is to find the difference in Length of the multivalued field string with commas, and without commas - to find the number of commas, and add 1 to find the number of target pests in the multivalued field. After this is done, I can divide the cost of the application by the number of pests to provide an accurate cost for the application based on target pest.
With that said, this length subtraction creates #Type! errors in report views and keeps that specific text box control from being summed, averaged, or undergoing any other header/footer type operations other than "count."
Is there a way to count individual values in a multivalued field without using len(), or is there a specific level (such as query or table level) where I can calculate or find this count explicitly/directly and have it properly referenced from the report in such a manner that I can perform sums, averages, etc. to the data?
If you are even close to grasping this, or need clarification, please feel free to ask any other questions or request a stripped down version of the DB in question. I'll be back to work on this in 16 hours.
Thanks in advance!
However, sometimes we record applications of chemicals which target multiple pests. When this occurs, grouping remains the same, but records containing multiple target pests will be present in the groups for each of the pests targeted. This is also expected and desired, but with an unwanted consequence. This duplication is a good, realistic and truthful representation of chemical applications, but in terms of representing the cost to spray for each pest, the duplication of costs is false and misleading representation.
My attempt to solve this problem was to divide the cost associated with each record by the amount of pests each record targets (on a per record basis in a text box control). I.e. a record of an application which targets 3 pests would show 1/3 of the total cost of that application as it's cost in this report. The record would show up three times (once in each pest's group), and the total sum of costs would remain accurate.
One problem I run into when performing this calculation is that I cannot make a direct reference to the number of target pests of a specific chemical application because they are entered in a multivalued field. One way of extracting this count is to find the difference in Length of the multivalued field string with commas, and without commas - to find the number of commas, and add 1 to find the number of target pests in the multivalued field. After this is done, I can divide the cost of the application by the number of pests to provide an accurate cost for the application based on target pest.
With that said, this length subtraction creates #Type! errors in report views and keeps that specific text box control from being summed, averaged, or undergoing any other header/footer type operations other than "count."
Is there a way to count individual values in a multivalued field without using len(), or is there a specific level (such as query or table level) where I can calculate or find this count explicitly/directly and have it properly referenced from the report in such a manner that I can perform sums, averages, etc. to the data?
If you are even close to grasping this, or need clarification, please feel free to ask any other questions or request a stripped down version of the DB in question. I'll be back to work on this in 16 hours.
Thanks in advance!
