SUM() issue

```Hi, i have a question for y'all, i have a table

D    P       Partner
---------------------------
1    3           P
5    2           P
6    4           K
8    3           K
7    8           P
7    5           K
2    3           K

Ok, what i need, is to find a difference between the SUM(of D column
where the Partner is P) and SUM(of P column for all partners)...

So, the result should be SUM(D where Partner = P) - SUM(P)

If anyone has any ideas, please share it with me,....THANX!!

```
 0
ApeX
6/21/2007 11:26:55 PM
access.queries 6343 articles. 1 followers.

5 Replies
680 Views

Similar Articles

[PageSpeed] 18

```"ApeX" <mmojas@gmail.com> wrote in message
> Hi, i have a question for y'all, i have a table
>
> D    P       Partner
> ---------------------------
> 1    3           P
> 5    2           P
> 6    4           K
> 8    3           K
> 7    8           P
> 7    5           K
> 2    3           K
>
> Ok, what i need, is to find a difference between the SUM(of D column
> where the Partner is P) and SUM(of P column for all partners)...
>
> So, the result should be SUM(D where Partner = P) - SUM(P)
>
> If anyone has any ideas, please share it with me,....THANX!!
>

ApeX,

Table:

Create a blank MS Access database.  You can copy and paste this DDL SQL query into an MS
Access Query, executing it in order to create the table.

I could not detemine your primary key, so I added a column for that.

CREATE TABLE DPPartner
(DPPartnerID           AUTOINCREMENT
,D                     INTEGER
,P                     INTEGER
,Partner               TEXT(1)
,CONSTRAINT pk_DPPartner
PRIMARY KEY (DPPartnerID)
)

Sample Data:

As above.

Query:

SELECT (SELECT SUM(D0.D)
FROM DPPartner AS D0
WHERE D0.Partner = "P")
-
SUM(D1.P) As Difference
FROM DPPartner AS D1

Results:

Difference
-15

Sincerely,

Chris O.

```
 0
Chris2
6/22/2007 1:05:38 AM
```SELECT Partner
, (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
FROM TheTable
GROUP BY Partner

Should Return
Partner : TheDiff
K   :  -13
P   :  -23

If you want to restrict it to on specific partner then add a where clause
SELECT Partner
, (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
FROM TheTable
WHERE Partner = "P"
GROUP BY Partner

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"ApeX" <mmojas@gmail.com> wrote in message
> Hi, i have a question for y'all, i have a table
>
> D    P       Partner
> ---------------------------
> 1    3           P
> 5    2           P
> 6    4           K
> 8    3           K
> 7    8           P
> 7    5           K
> 2    3           K
>
> Ok, what i need, is to find a difference between the SUM(of D column
> where the Partner is P) and SUM(of P column for all partners)...
>
> So, the result should be SUM(D where Partner = P) - SUM(P)
>
> If anyone has any ideas, please share it with me,....THANX!!
>

```
 0
John
6/22/2007 11:28:38 AM
```John,

Any idea how Access actually processes this (query plan)? I would think that
it would process the (SELECT SUM(P) FROM TheTable) section for each grouping.
Would it be more efficient (faster) to do something like:

SELECT yourTable.Partner, Sum(yourTable.D) - Tot.SumofP as theDiff
FROM yourTable, (SELECT SUM(P) as SumOfP FROM yourTable) as Tot
GROUP BY yourTable.Partner

I would guess that Access will process the subquery only once, in this case.

Dale
--

"John Spencer" wrote:

> SELECT Partner
> , (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
> FROM TheTable
> GROUP BY Partner
>
> Should Return
> Partner : TheDiff
> K   :  -13
> P   :  -23
>
> If you want to restrict it to on specific partner then add a where clause
> SELECT Partner
> , (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
> FROM TheTable
> WHERE Partner = "P"
> GROUP BY Partner
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "ApeX" <mmojas@gmail.com> wrote in message
> > Hi, i have a question for y'all, i have a table
> >
> > D    P       Partner
> > ---------------------------
> > 1    3           P
> > 5    2           P
> > 6    4           K
> > 8    3           K
> > 7    8           P
> > 7    5           K
> > 2    3           K
> >
> > Ok, what i need, is to find a difference between the SUM(of D column
> > where the Partner is P) and SUM(of P column for all partners)...
> >
> > So, the result should be SUM(D where Partner = P) - SUM(P)
> >
> > If anyone has any ideas, please share it with me,....THANX!!
> >
>
>
>
```
 0
Utf
6/22/2007 3:49:01 PM
```You may be and probably are correct.  It would depend on how well the query
interpreter was written.  The interpreter might be smart enough to figure
out that the subquery was not correlated and only run it one time.

And I note that my SQL is missing the word "FROM" in the calculated field
(SELECT Sum(P) FROM TheTable)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dale Fye" <dale.fye@nospam.com> wrote in message
news:EDDDEFDE-A4F4-4E50-BE48-1FDE17F98F8B@microsoft.com...
> John,
>
> Any idea how Access actually processes this (query plan)? I would think
> that
> it would process the (SELECT SUM(P) FROM TheTable) section for each
> grouping.
> Would it be more efficient (faster) to do something like:
>
> SELECT yourTable.Partner, Sum(yourTable.D) - Tot.SumofP as theDiff
> FROM yourTable, (SELECT SUM(P) as SumOfP FROM yourTable) as Tot
> GROUP BY yourTable.Partner
>
> I would guess that Access will process the subquery only once, in this
> case.
>
> Dale
> --
> Email address is not valid.
>
>
> "John Spencer" wrote:
>
>> SELECT Partner
>> , (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
>> FROM TheTable
>> GROUP BY Partner
>>
>> Should Return
>> Partner : TheDiff
>> K   :  -13
>> P   :  -23
>>
>> If you want to restrict it to on specific partner then add a where clause
>> SELECT Partner
>> , (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
>> FROM TheTable
>> WHERE Partner = "P"
>> GROUP BY Partner
>>
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "ApeX" <mmojas@gmail.com> wrote in message
>> > Hi, i have a question for y'all, i have a table
>> >
>> > D    P       Partner
>> > ---------------------------
>> > 1    3           P
>> > 5    2           P
>> > 6    4           K
>> > 8    3           K
>> > 7    8           P
>> > 7    5           K
>> > 2    3           K
>> >
>> > Ok, what i need, is to find a difference between the SUM(of D column
>> > where the Partner is P) and SUM(of P column for all partners)...
>> >
>> > So, the result should be SUM(D where Partner = P) - SUM(P)
>> >
>> > If anyone has any ideas, please share it with me,....THANX!!
>> >
>>
>>
>>

```
 0
John
6/22/2007 5:55:40 PM
``` Duguje    Potrazuje       Partner    PartnerName   Date

-------------------------------------------------------------------------------------
1              3                  P        Tisak
12.3.2006
5              2                  P        Croatia
25.6.2006
6              4                  K         HT
15.6.2007
8              3                  K         Tisak
11.11.2005
7              8                  P         Croatia
3.5.2007
7              5                  K         HT
11.3.2005
2              3                  K         HT
5.1.2005

Ok, what i need, is to find a difference between the SUM(of D column
where the Partner is P) and SUM(of P column for all partners)...

So, the result should be SUM(D where Partner = P) - SUM(P)

-----------------------

Hey guys, thanx i figured it out, at least i think :)), ok now i
changed the table a bit, so the query looks now

SELECT partner, PartnerName, (SELECT SUM(Duguje)
FROM arhivdugujepotrazuje
WHERE datumdos <= '01-01-2007' and
partner = 'p' and PartnerName = 'croatia')
-
SUM(Potrazuje) As Dospjelo FROM arhivdugujepotrazuje
where partner = 'p' and PartnerName = 'croatia'
group by partner, PartnerName

.....but the problem is following, when i put the 'partner = 'some
name' and PartnerName = 'some name' i get what what i want, it is:

the result:

partner  PartnerName   dospjelo
------------------------------------------------
P            Croatia             -5

....
but when i don't wanna put the condition "where" with the partner and
partnername, that is, when i wanna show all grouped partner &
partnername i get some strange results :

example:

SELECT partner, PartnerName, (SELECT SUM(Duguje)
FROM arhivdugujepotrazuje
WHERE datumdos <= '01-01-2007')
-
SUM(Potrazuje) As Dospjelo FROM arhivdugujepotrazuje
group by partner, PartnerName

partner  PartnerName   dospjelo
------------------------------------------------
P         Croatia             13
K           HT                  11
K         Tisak                20
P         Tisak                20

If anyone has any ideas, please share it with me,....THANX!!

```
 0
ApeX
6/23/2007 12:12:55 PM

Similar Artilces:

Excel template issues
I have a user that has a excel template on his network home dir NW 5.1 Server he says that after he creates a new file from that template - he goes to open and it tells him read only access running Excel 2000 thanks ...

Outlook Desktop Client 4.0 issue
While installing the Outlook Client .net failed to install. Can't seem to get pass the error. Does any one know what the issue might be. The machine is an IBM Thinkpad T43. I'm also having issues with Outlook restarting itself every five minutes on other machines after the client has been installed. Help!!! A.J. download the .Netframework locally from google and install it and then run the setup. /Aamir ...

Migrating XCHNG 5.5 to 2K3...ADC Issue
I'm in the process of testing the migration of an XCHNG 5.5 (NT 4 domain) to an XCHNG 2K3 server (W2K member server) in a W2K3 AD domain. I'm running the latest deployment tools (06.05.7226)... I have just installed the ADC connector and I'm running the ADC tools as the Exchange server admin (member of AD domain admins and place in org, site, configuration container of XCHNG 5.5 with Service Account Admin permissions). All mailbox users have been migrated already to AD domain...with one-to-one relationship. Issue: When I run the ADC tool Data Collection (Step #2), it does not ...

Gmail & Microsoft Outlook Issue
Hello everybody! I'm experiencing a problem to retrieve email from Gmail on Microsoft Outlook 2002. I set everything the way Gmail recomends it... turning on the SSL authentication for both SMTP and POP3 servers.. using ports 995 and 465 (respectively)... activating the SMTP verification for outgoing mail and when I push on the "Test Account Settings" everything is alright: it finds the outgoing mail server, the incoming mail server, it logs onto the incoming mail server BUT (huge big) it's unable to send a test message e-mail... How is that possible ??? It sends the follow...

Office XP Exel
Scenario: User's A,B, C, and D all have access to an excel XLS on a 2003 server. Users A & B have modify rights, users C&D only have read rights. If user A updates the file, the general tab in properties reflect the exact time the file was modified. After user A saves and closes the file, user D goes in. The changes are there, but in the properties general tab, the modify date in an old date (probably the actual creation date). Is this normal ? Any idea's ? ...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

Strange issue with OWA not displaying correctly.
Hi, Working on a Exchange 2003 sp1 server. When OWA is launched and the credentials are entered, the next screen looks almost like a "FTP" site! Any ideas where to start looking? Thanks Do you have the Directory Browsing option selected on the default web site (and hence the Exchange virtual directories under it)? -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Pete" <Pete@discussions.microsoft.com> wrote in message news:EEF3DF67-9793-4896-9FAC-158100D1F322@microsoft.com... > Hi, > Working on a Exc...

Issue with Loan Accounts
Hi Everyone, I have been having this issue for a while - but just now trying to figure out what's happening. I have several loans (including car, equity, etc.) which I have set up on Money 7. Sometimes the interest and principle are calculated correctly - but more often, the entire sum of the payment is allocated to principle (as viewed in the account register). I've check to insure that the loan is set up okay - and it looks okay to me. Any ideas what's happening to me? Any help appreciated. Thanks. Patrick Hi, I was really hoping someone would respond to this. My ...

Excel Tends to access network when I issue commands
Excel on my PC usually access network when I issue the first command (or after stopping activities on Excel for a while). My system will "freeze" and I see the LAN icon on system tray light up - indicating the PC is communicating with the LAN server. Is this due to some macro or other options that I have previously chosen ? This sometimes happens when I am working on my powerpoint. Can Excel and PowerPoint work properly without doing this "freeze" and communicating with server ? It is taking up time and my patience !! ...

I have created a sheet which is tracking 3 items per client and a number of clients so I have set up a SUM function the only way I know how which is a long a1+a4+a7, etc Its works fine, but if I want to add a new client and 3 new rows, ALL of the + after are now off. Is there a better way to create a formula that will track say every 3rd cell in a column but then let you lengthen or shorten the column? Thanks!! -- ExcelNoob123 In searching her I have figured out it is the DSUM function that I ma needing. I have tried a number of times - seems simple enough but I cant get it to work ...

Terminal Servers in Cluster - Login/Profile Issues
Hello Gurus, Currently I am having an issue with logins and profiles as per details below. Server/Network Configuration Details: 1 X Windows Server 2008 Std FE 64bit (DC) 2 X Windows Server 2008 Ent 32bit Terminal Servers User Profiles are Roaming and exist in: \\SBSERVER\Profiles on the Domain Controller and C:\Users\ on the Terminal Servers. Approx 50 users. Problem Description Error messages when logging in the terminal servers: Your roaming profile is not synchronized correctly with the server. Windows will load your previously-saved local profile instead. See the pre...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

Credit Issue
Hi I just live in January, most of thing are good, but I have a big trouble now. As GP provide very little function on Credit tracking, I lost the function of tracking credit issued against item.( Such as warranty item, or show room display item) Meanwhile, I didn't find any user defined area, comment area or enough space to put more inforamtion about the credit on the GP credit. Customer will don't know what the credit is for. It is just terrible!!! If GP could allow crredit be issued through SOP with no return or on other type of item, and link to GL automatically by changing...

Outlook 2007 scrolling issue
Hello everyone, I am running Outlook 2007 on Windows Vista Home Premium. My problem is jerky scrolling in my inbox and in email messages. I am running this on a laptop and have updated the driver for my touchpad. Any suggestions on how I can get this to stop? Before 2007, I ran 2003 with no scrolling issues. Thanks in advance for any help offered. Missy ...

Sum Times
I have four fields on a form to show time. I want a seprate "Total" field to add the time between the first two fields and then add the time between the second two fields. Like this: In LunchOut LunchIn Out Total 6:00am 12:00pm 12:30pm 4:30pm 10 hours The first four fields are stored as medium times. Can someone let me know how to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 "ladybug via AccessMonster.com&quo...

Sum only positive values
Dear all, In my table the values can be positive or negative. I'm trying to do a consult to sum only the positive values... How can I do it? Thanks in advance! Andr=E9. Add the field to the query once more, remove the show flag & enter the condition ie SELECT Sum(MyFieldd) AS TheSum From MyTable Where MyField >0 HhH Pieter <gatarossi@ig.com.br> wrote in message news:1191407027.588326.298520@o80g2000hse.googlegroups.com... Dear all, In my table the values can be positive or negative. I'm trying to do a consult to sum only the positive values... How can I do i...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....