trying to get a sum in access 2003

  • Thread starter Thread starter MNJoe
  • Start date Start date
M

MNJoe

access 2003, I have a query with 2 tables, Payable and Vendor, 4 fields
"Vendor_ID", "Total_Amount" and "Invoice_date" from the Payable table and
"Name" from the Vendor table. The join is Vendor_ID from Payable table = "ID"
from the Vendor table. The selection is from dates entered in by the user
from a form "Beginning_Date" and "Ending_Date" for the "Invoice_Date" from
the Payable table using the expression in the query "Between
[Forms]![Top_Vendors_Form]![txtBeginning_Date] And
[Forms]![Top_Vendors_Form]![txtEnding_Date]" I can get all the records out
to the report just fine and it all looks good.

So, How can I SUM up the "Total_Amount" field in the query and just have one
record for each name\ID. I click the Sigma icon and add the totals row and
have been trying several things and just can't get it. If I change total row
from "Group by" to "SUM" under the "Total_Amount" and blank out the total row
on the other 3 columns. I get an error "You tried to execute a query that
does not include the specified expression 'Vendor_ID' as part of an aggregate
function". (Vendor_ID is the first column selected in the query). If I leave
the other 3 at "Group by" then the error goes away but, I get all the records
and the "Total_Amount" column is not summed up.
 
Have GROUP BY under the vendorID field, have SUM under total_amount, have
WHERE under the field used for the criteria for supplying the interval of
time you are interested to get. If any other column is present, either
remove it if it does not really matter, either have LAST if you still want
to see the information that is repeated anyhow (such as VendorName, if there
is such a field), either have a GROUP BY if the summation of the
total_amount has to be broken, say, by vendor by ... that field.


Vanderghast, Access MVP
 
Back
Top