By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Sum columns in Access table

P: n/a
I am working with an Access table that contains dollar amounts in two
columns, one for January 2004 (CP_Payment_01 ) and the other for
February 2004 (CP_Payment_02). My original goal was to take this table
and example of which is below - and sum the values in each by Vendor
Nbr.

Table: RealEstateRent
Company Vendor_Group Vendor Nbr Vendor Name CP_Payment_01
CP_Payment_02
------- ------------ ---------- ----------- -------------
-------------
20 20 1000 Hugh Rents 140.00 155.31
20 20 1000 Hugh Rents 472.20 500.60
20 20 1000 Hugh Rents 180 225
20 20 1002 MacTavish 100.00 300
20 20 1002 MacTavish 200.00 445.00
Final Result:
Company Vendor_Group Vendor Nbr Vendor Name SumCP_Payment_01
SumCP_Payment_02
------- ------------ ---------- ----------- ----------------
----------------
20 20 1000 Hugh Rents 792.20 880.97
20 20 1002 MacTavish 300.00 745.00

The current SQL statement that I am using that does not work is:

SELECT DISTINCTROW Company, Vendor_Group, Vendor_Nbr,
Sum(CP_Payment_01) AS [Sum Of CP-PMT-01], Sum(CP_Payment_02) AS [Sum
Of CP-PMT-02]
FROM RealEstateRent
GROUP BY Company, Vendor_Group, Vendor Nbr, Vendor Name;
This Sql Statement built in Access returns:
Company Vendor_Group Vendor Nbr Vendor Name CP_Payment_01
CP_Payment_02
------- ------------ ---------- ----------- -------------
-------------
20 20 1000 Hugh Rents 140.00 155.31
20 20 1000 Hugh Rents 472.20 500.60
20 20 1000 Hugh Rents 180 225
20 20 1002 MacTavish 100.00 300
20 20 1002 MacTavish 200.00 445.00

I know my primary problem is a lack of understanding about how SQL
summarizes fields. What must I do to get the needed result?

Desperately Seeking to Improve My SQL Skills,

richie
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
use a totals query...
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.