Cindy8268
Sep 16 2008, 01:30 PM
Hi - I'm trying to fix a report that needs an annual average of deliveries made in tons to multiple places. The report I have totals the tons just fine but when I try to average it against the acres that the tons of deliveries go to, it adds the acres each time there's a delivery to the same field. So my problem is figuring out how to get Access to just count the acres once per field, even though there are multiple deliveries to that field, total the acres for the year and divide that into the tons delivered to get the average tons delivered per acre.
I hope this is clear enough to understand and someone can help. Thanks in advance.
jwhite
Sep 16 2008, 07:14 PM
When asking how to properly construct a query, it's a good idea to post what you have tried. Perhaps something like this is what you are looking for:
CODE
tblFieldDeliveries
---------------------------------------------
MyPK FieldID TonsDelivered DateDelivered
1 1 10 02/08/08
2 2 40 03/08/08
3 1 20 08/08/08
4 2 50 09/08/08
5 3 10 11/11/08
6 3 10 12/11/07
qryFieldDelieryAverage
---------------------------------------------
SELECT [FieldDeliveries].FieldID, Avg([FieldDeliveries].TonsDelivered) AS AverageTons
FROM FieldDeliveries
GROUP BY [FieldDeliveries].FieldID, Year([DateDelivered])
HAVING (((Year([DateDelivered]))=2008));
Query Output
---------------------------------------------
FieldID AverageTons
1 15
2 45
3 10
Cindy8268
Sep 17 2008, 01:52 PM
Thank you for your reply but that's not quite what I need. Each field may have multiple deliveries but I need somehow to only count the acres for that field once and get an average tons per acre delivered. And then I need to total the acres for all the fields we deliver to (again, only count the acres for each field once) and all the deliveries and get an average tons per acre delivered. I'm not sure if you do that in a query or within a report, which is where I was trying to do it.
MtnGoat
Sep 17 2008, 03:00 PM
I'd start with what John has suggested but include the number of acres per field. Actually, you should construct a separate table with field specifications, like acreage, crop, etc. so that your data is normalized. You could then create a report using a query that links the delivery table with the field table as your report data source.
If you used the report wizard, you could group the report by field so that you have a field header secton. Leave everything else in the detail section, delivery date/tons/delivered by/etc., for now.
Open the report in design view and in the field header section create a text box with the following control source:
=Sum([Tons])/[Acres]
The items in brackets [ ] are field names. This should give you the average tons per acre per field. You could also create a text box that provides the total tons delivered per field by using the first part of the above formula.
At some point you may wish to group by date or some other characteristic. You can do this via the wizard as well.
Good luck and welcome to UA!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.