Simple Question on Addition

I searched the discussion groups for the answer to what seems to be a simple 
question, but found nothing. I have a group by query with three columns:

itemNo
Debits
Credits

The query is grouped on the first column, itemNo.

I want to add the Debit column to the credit column and show the result in a 
third field, Total. For each record, the Debit column contains currency 
amounts. The Credit column contains either negative currency amounts or is 
null.

Here is my nonfunctioning SQL. When I run the query, the Total column is 
blank for every record.

SELECT eBayFees_lastMonth.[Item number], Sum(eBayFees_lastMonth.Debits) AS 
SumOfDebits, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits, 
Sum([eBayFees_lastMonth.Debits] + [ebayFees_lastMonth.Credits]) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

Any ideas?

Thanks,
GwenH




0
Utf
12/1/2009 4:49:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
515 Views

Similar Articles

[PageSpeed] 42

Gwen

What happens when you run that query?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"GwenH" <GwenH@discussions.microsoft.com> wrote in message 
news:87C0E533-2CD4-4909-BB8F-BFBB98A50729@microsoft.com...
>I searched the discussion groups for the answer to what seems to be a 
>simple
> question, but found nothing. I have a group by query with three columns:
>
> itemNo
> Debits
> Credits
>
> The query is grouped on the first column, itemNo.
>
> I want to add the Debit column to the credit column and show the result in 
> a
> third field, Total. For each record, the Debit column contains currency
> amounts. The Credit column contains either negative currency amounts or is
> null.
>
> Here is my nonfunctioning SQL. When I run the query, the Total column is
> blank for every record.
>
> SELECT eBayFees_lastMonth.[Item number], Sum(eBayFees_lastMonth.Debits) AS
> SumOfDebits, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits,
> Sum([eBayFees_lastMonth.Debits] + [ebayFees_lastMonth.Credits]) AS Total
> FROM eBayFees_lastMonth
> GROUP BY eBayFees_lastMonth.[Item number]
> ORDER BY eBayFees_lastMonth.[Item number];
>
> Any ideas?
>
> Thanks,
> GwenH
>
>
>
> 


0
Jeff
12/1/2009 4:58:33 PM
Use the NZ function (or an IIF statement) to force a value if Credits is Null

SELECT eBayFees_lastMonth.[Item number]
, Sum(eBayFees_lastMonth.Debits) AS SumOfDebits
, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits
, Sum([eBayFees_lastMonth.Debits] + Nz([ebayFees_lastMonth.Credits],0)) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

Using an IIF statement:
SELECT eBayFees_lastMonth.[Item number]
, Sum(eBayFees_lastMonth.Debits) AS SumOfDebits
, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits
, Sum(Debits + IIF(Credits is Null,0,Credits)) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

GwenH wrote:
> I searched the discussion groups for the answer to what seems to be a simple 
> question, but found nothing. I have a group by query with three columns:
> 
> itemNo
> Debits
> Credits
> 
> The query is grouped on the first column, itemNo.
> 
> I want to add the Debit column to the credit column and show the result in a 
> third field, Total. For each record, the Debit column contains currency 
> amounts. The Credit column contains either negative currency amounts or is 
> null.
> 
> Here is my nonfunctioning SQL. When I run the query, the Total column is 
> blank for every record.
> 
> SELECT eBayFees_lastMonth.[Item number], Sum(eBayFees_lastMonth.Debits) AS 
> SumOfDebits, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits, 
> Sum([eBayFees_lastMonth.Debits] + [ebayFees_lastMonth.Credits]) AS Total
> FROM eBayFees_lastMonth
> GROUP BY eBayFees_lastMonth.[Item number]
> ORDER BY eBayFees_lastMonth.[Item number];
> 
> Any ideas?
> 
> Thanks,
> GwenH
> 
> 
> 
> 
0
John
12/1/2009 5:27:43 PM
SELECT eBayFees_lastMonth.[Item number],
  Sum(eBayFees_lastMonth.Debits) AS SumOfDebits, 
  Sum(eBayFees_lastMonth.Credits) AS SumOfCredits, 
  Sum([eBayFees_lastMonth.Debits]) + 
          IsNull(Sum([ebayFees_lastMonth.Credits]),0) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"GwenH" wrote:

> I searched the discussion groups for the answer to what seems to be a simple 
> question, but found nothing. I have a group by query with three columns:
> 
> itemNo
> Debits
> Credits
> 
> The query is grouped on the first column, itemNo.
> 
> I want to add the Debit column to the credit column and show the result in a 
> third field, Total. For each record, the Debit column contains currency 
> amounts. The Credit column contains either negative currency amounts or is 
> null.
> 
> Here is my nonfunctioning SQL. When I run the query, the Total column is 
> blank for every record.
> 
> SELECT eBayFees_lastMonth.[Item number], Sum(eBayFees_lastMonth.Debits) AS 
> SumOfDebits, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits, 
> Sum([eBayFees_lastMonth.Debits] + [ebayFees_lastMonth.Credits]) AS Total
> FROM eBayFees_lastMonth
> GROUP BY eBayFees_lastMonth.[Item number]
> ORDER BY eBayFees_lastMonth.[Item number];
> 
> Any ideas?
> 
> Thanks,
> GwenH
> 
> 
> 
> 
0
Utf
12/1/2009 5:54:01 PM
Reply:

Similar Artilces:

XML DOM question
Hi All, I use XML DOM to read and write XML files. I did not find any specification whether or not XML DOM locks files for reading and writing at the moment it reads or writes to the file. I have a system where different applications can read and write from and to XML files using XML DOM, so it is important to me to know whether or not the file that is being read is locked for writing and vice versa. Does anybody can help me with this issue or refer to a proper article? Thanks a lot in advance. -- Mark. How about singlenton pattern in your situation? We can't use singlenton th...

Mouse Rollover question
In our CRM 4.0 system, as you roll your mouse over a field lable (or hover over it), an information box pops up with the field name. This apparently is from the Department of Redundancy Department, as telling me the same thing as I already know is pointless. However, each attribute has a description field that should be used for further details about that field. Is there any way to get the value in the description field for an attribute to appear in the roll-over box, rather than the field name itself? This can be especially useful when a field name needs to be shorter than what we ...

Newbire Question: How to Transfer Outlook Address Book To Another PC?
Hello, I'm a newbie to Outlook as I've never used it before. I'm setting up a laptop for a friend and he wants his outlook address book transferred onto his laptop from his old PC. I have no idea how to do this, so if someone could help me or maybe there is a link that has the information, it would be appreciated. jeff <Zombieboy@hotmail.com> wrote in message news:so3dl1tv64qnr8d738l0t5brk4kto2pp5m@4ax.com... > Hello, > > I'm a newbie to Outlook as I've never used it before. I'm setting up a > laptop for a friend and he wants his outlook address...

Can't add additional components
When I try to add an additional component to Pub 2003 from my installation CD (using Add/Remove Prog.) I get the error that I have an incorrect pro11.msi file, both on the installation CD and the one on my system. I think that this may be because of Office Update changing something. Does anyone know how to resolve this? "This installation package could not be opened" error message when you try to run Office 2003 Setup http://support.microsoft.com/default.aspx?scid=kb;en-us;828380 How to use a setup log file to troubleshoot setup problems in Office 2003 http://support.microsoft....

A simple XML transformation
Hi all, I have some XML that looks kind of like this: <TopLevel> <file path = ".\SomePath\SomeFile.xxx" /> <file path = "C:\SomeFolder\SomeFile.xxx" /> </TopLevel> I want to replace every file path that meets a simple criteria (the path begins with "C:\Something") with a different path. I know how to do this sort of thing using regular expressions, and I could figure out how to do it using an XMLReader, but I'm sure there is a 3-line solution using some XPath magic. 1. Can some kind soul please post a simple example...

Questions on Exchange 2003 (smtp & exch attributes)
Good day, Well bit by bit my exchange migration is proceeding. I'm quite impressed with the tools available for this procedure and hope the project wraps up without incident (touch wood). Having said that, I do have 2 questions - one on smtp (5.5 <> 03) that wasn't really covered in the deployment guide and another on recipient exchange attributes (which is just due to my inexperience with the product). SMTP. - Our 5.5 server (192.168.0.1) is currently running IMS and a smtp connector to the 2003 box, mail flows both ways fine. Our firewall routes mail from our valid...

Visio 2007
Is there a simple hook from Visio 2007 and an Active directory Environment to do and export/import from AD to Visio. I want to create a simple drwaing of my tree - Root - OU's etc.. Hello, yes there is: www.netdocad.com -- Mit freundlichen Grüßen/ with kind regards Senaj Lelic DE MVP Visio "Bhoros" <Bhoros@discussions.microsoft.com> schrieb im Newsbeitrag news:84DA2C84-5934-45D1-A75D-5EC06163DCDD@microsoft.com... > Is there a simple hook from Visio 2007 and an Active directory Environment > to > do and export/import from AD to Visio. > >...

007 questions
How do I add some new chart styles (say, with different line widths)? Is it possible to record a macro that changes the chart size? (I've recorded it, but nothing happens when I run it) Has anyone developed a data labels macro or add-in yet? I haven't gotten around to investigating the new chart template mechanism. I have gone through macro recording, though. Do you still have 2003? Record the macro there. It may not take into account the new formatting features, but the macro still ought to work in 2007. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solut...

Event Sounds Question
I assign wav files to system events. Certain ones won't play when when I assign it to the Windows Logon event. It previews fine and plays in Windows Media Player I thought the length was the problem 'cause these wav files are 22 and 24 seconds but I assigned a wav file to the Windows Logon that is 30 seconds and it plays fine. Although the length does have an impact. Any ideas?? James ...

FE/BE question
Hi, When an environment has a single FE and BE, during the initial setup, do the permissions change on Exchsrvr folder of either Exch server, or do they simply retain the default permissions (as seen in KB 322935)? thanks, Mel On Thu, 12 Oct 2006 04:54:02 -0700, Mel <Mel@discussions.microsoft.com> wrote: >Hi, > >When an environment has a single FE and BE, during the initial setup, do the >permissions change on Exchsrvr folder of either Exch server, or do they >simply retain the default permissions (as seen in KB 322935)? > >thanks, > >Mel The perms st...

Question #2
Hello! I'm new to the group and I'm working on an excel sheet. What I'm trying to do is, in a column have a number ex. 36 repeat that number 72 times and on the 73 row increase the number 36 by 1 (to 37). I need to do it about 730 times. I don't know where to start. I've tried to do an auto fill but it's increments by a decimal. The sheet should look like this. Column A 36 36 36 36 36 (72 times) 37 37 37 (72 times) Can anyone help? Thanks, Darrell --- Message posted from http://www.ExcelForum.com/ One way, put in row1 =FLOOR(36+(MOD(ROW(1:1)/73,72)),1)...

Using LVN_HOTTRACK question
In CListCtrl, is it possible to prevent the item color change (if using LVN_HOTTRACK notification) when mouse hovering over an item? Thanks in advance ...

outlook 2003 question
Hi All, I am using windows xp home sr3 with outlook 2003. I have several e-mail accounts one I use with outlook express the other I use with outlook 2003. Can I set up outlook with the two accounts and have them go into separate in boxes? Can this be done? Any help would be most appreciated. thanks, -- Regards, Gary Metzler send to: gmtravel@bellsouth.net http://www.outtasighttravel.com Msn messenger: gmetzler1150@hotmail.com Skype name: garymetz pop accounts? or? If pop accounts generally you create folders to sort mail on receipt / send, appropriately ...

Simple Valication Check... Question
Using VS2008 I have an app that has an edit box that contains the Clients Age. I'm doing the validity check when the edit box looses focus. This works great when the user clicks a button that runs the application. However, if the user clicks Run App from the menu, the edit box does not loose the focus... therefore the check is not made. I can tell you that the code for File>Run does nothing more that call the ::OnFileRunapplication What's the easiest way to resolve this? Seems like I need to loose the focus of the edit box before making a call to ::OnFileRunap...

Couple of outlook 2000 questions
I have a couple of Outlook 2000 question I would appreciate the answer to if anybody can help, Outlook is standalone and not connected to an exchange server: When starting a new e-mail the reciepents name is entered in the to box e.g. tim then the subject is entered but the name does not change to the e-mail address even though Tim is in the address book?? How do I get the address of the sender of an e-mail to be entered in to my address book when I reply to their e-mail. Tim Rather a confusing post. You did not post enough information here to permit a guess, let alone an answer. Outlo...

OL 2003 Data File question
Hi. Right now I have one .pst file for my Outlook and everything runs fine. My question is this: Does Outlook 2003 run slower as the .pst data files grow larger. I have many emails I need to keep for business reasons and although it might be ok now, will my Outlook run slow, say, a year or so from now; once the .pst file has grown to say a gigabyte or more, etc.? Any help would be appreciated. Thanks. Andrew ...

VB to have first letter Capital Question
I'm using a data input form to add records to my table. I'm collecting address information and want the first letter of each word to be capital in the address field. I searched this site and found some code the would accomplish this however when I use it, I get errors. I'm wondering if it is because the address contain numberic values, then a street name followed by Street, Lane, Blvd. etc. Can you take a look at this and see why it is generating an error.. Private Sub CHAPADD_AfterUpdate() If StrComp(Me!txtCHAPADD, LCase(Me!Text), 0) = 0 Then Me!txtCHAPADD = StrConv...

Access 2007 and MDE file Question
I have a Access 2003 MDE file that links to an Access 2003 MDB file. I understood that Access 2007 is not able to work with an MDE file. This is according to Microsoft KB article: http://technet.microsoft.com/en-us/library/cc178973.aspx However, unbenknownst to me, one of my customers somehow installed Access 2007 on a machine and ran the MDE file with it. I saw it with my own eyes! The application had been working fine for years, but now all the sudden they claim it is "losing" data. First, how come it can even run the MDE file? Second, could the Fact that i...

When Excel workbook starts I want to ask a question first
Thanks for trying to help.... When a coworker opens an Excel file, I want Excel to first have the person enter a number... Once the number is entered, that number is matched from column A and the sheet will only display the contents of that matched "row". Am I making sense? We just don't want to open the spreadsheet with all that data in thier face and have them search for their own stats. Again thanks! Hi Steve, Probably a couple of ways, depending on how the data is assembled. Perhaps a combination of a drop-down list with the numbers and a vlookup formula. Or a workshe...

Simple Access Query/Form Question
Hello all, I know this may be a stupid question but i'm a newbie to Access. Here the background on what I'm designing. I'm creating a database to track special orders for our store customers. Employees enter information about the order including customers name, contact information, item to be ordered and etc. The main form has multiple check box to note if the customer has paid, the order has been placed, arrived, when the customer was notified and when the customer picks up the item. I'm looking to create a query that checks the field to see if a specific check box is checke...

autonumbering question
hi all, I have office 2007. Im using access. I have two colums of numbers, the first, column 1 is for a context number, and i enter it by setting a value manually in design view i.e. 140. i want to enter a list of items numbered 1 to 50 in the second column, column 2. when i use auto number it lists the sequence but when i change the value in column 1 for the next context i.e. 142... the numbers contuinue in colum 2 i.e. 51 52 53 but i want them to start again at 1 2 3 etc... how do i do this? any help is greatly appreciated well - as you have found, you can't use Autonu...

2 questions about connectors and replication
We are currently running an exchange server that replicates to another exchange server in Frankfurt, Germany. It has been setup in Exchange and the DNS records so that anyone sending or receiving email goes through the german server first and our server second. What we would like to do is sever the replication and host our own email without going through them first. My first question is whether or not there is anything I need to do as far as our exchange server goes, besides removing the "frankfurt" connector and adding a new local connector? The second question is what is ...

Mac user sending Japanese characters are received as question mark
I have a Mac user using Safari to access OWA hosted on a Exchange 2003 server. He can read and type Japanese characters but when he sends a new message or a reponse to a message the Japanese characters are received as question marks. Does anyone have any idea how to fix this? Steve <Steve@discussions.microsoft.com> wrote: >I have a Mac user using Safari to access OWA hosted on a Exchange 2003 >server. He can read and type Japanese characters but when he sends a new >message or a reponse to a message the Japanese characters are received as >question marks. Does any...

Can't edit SQL question in MS Excel/Query
Hello, I'm running Win2k SP3, Excel 2000 SP-1 and have created SQL questions that fetch data from an Oracle 8i DB via ODBC. Sometimes (or actually quite often) I can't edit the SQL questions in MS Query. The MS Query icon just flashes in the taskbar and disappears. Any wiz got any idea what can be done about it? Regards Hans Hans I'm not sure about the MS Query icon you are talking about in the taskbar but the way I can get to MS Query using Excel 2000 and connectiong to Oracle database is choosing 'Data' followed by 'Get External Data', and then choosing '...

Another IMF V2 question
I just reinstall SP2 on my Exchange 2003, and I still don't see the IMF option under my default SMTP virtual server. And YES I have checked the box (Apply IMF filter) in the Advanced propreties on the Default SMTP virtual server. Anyone has an idea? REgards JP The only thing on the advanced properties of the SMTP virtual server is to turn on/off the different filtering options. IMF settings are found under ESM > Global Settings > Right Click on Message Delivery > Intelligent Message Filtering tab. "JP Breton" <jpbreton@videotron.ca> wrote in message ...