473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Sum columns in Access table

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
1 10299
use a totals query...
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

32
by: Rich | last post by:
I'm sure it sounds kinda nutty to display 200 columns and 500,000 rows of data. But I have been pulling data from a Lotus Notes database into Sql Server for a while now, but Lotus Notes is...
3
by: Dave Crypto | last post by:
Hi There, SUMMARY: I need to know what the actual maximum date limit possible on a row of a MYSQL database. MORE DETAILS: For example, does a MYSQL database only allow 4032 bytes of data...
1
by: anonieko | last post by:
This example applies to javascript, table, cells, rows > > How do you access rows and columns of a HTML table? > > > <script language="javascript"> alert('start');
3
by: brianc | last post by:
I have a table that holds the employee id, a job name and then 5 columns (Proposal Phase Rating, Preconstruction Phase Rating, etc.) that hold a number rating 1-5. I've read that this design may...
8
by: Paul Hunter | last post by:
I am new to databases and thus to Access. I have a situation where I am trying to figure out how to key some tables I am working with. Consider that I have a database of my own records which are...
4
by: Mike | last post by:
I encountered this problem for the first time today while trying to add some columns to a query from a linked DBASEIII file. The new columns that I added on the design grid do not show up on the...
17
by: vish | last post by:
Hi all, I know this might look strange at first look, but i am looping thru a dataset created using to different tables from database.These two tables have some fields with the same names. ...
2
by: Nacho | last post by:
Hi, I'm designing a new database and I have a doubt in which surely you can help me. I'm storing in this database historical data of some measurements and the system in constantly growing, new...
6
by: napatel04 | last post by:
Hi everyone, I would like to know if there is a quick query someone can help me write for the following scenario. I think I can do this with VBA but since this is suppose to be a temp. solution,...
2
by: Dan | last post by:
Hello, all! New here, so please forgive if this has been answered before. I use MySQL most often, and can use the commands: show tables in ; show columns in ; which lists all the tables in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.