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