Avatar of Cristal638
Cristal638
 asked on

(500 pts.) Opposite of sum in a select query

Hi All,
This might be a simple one, but I can't seem to figure it out.  500 pts. because I need it right away.

I have a query that has 2 records per ID in it.  One for the run month and one for the prior month.

for example:

AsOf      ID      Shift
30-Jun-06      9113      -2.23096039
31-Jul-06      9113      0.278805355
30-Jun-06      9103      2.8866533
31-Jul-06      9103      1.297256548

What I want is the opposite of sum for each ID.
for example:

ID      NewShift
9113      2.509765745
9103      -1.589396753

Any help would be greatly appreciated.
Thanks,
Dave
Microsoft Access

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

Hi Cristal638,

SELECT ID, -Sum(Shift) AS NewShift
FROM YourTable
GROUP BY ID

Regards,

Patrick
Rey Obrero (Capricorn1)

just multiply the  field with min date with -1

GRayL

Can any body tell me what the relation is between the data and the result?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
peter57r

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

Ray,

> Can any body tell me what the relation is between the data and the result?

Good question.  I assumed that the example was incorrect, and went with a very literal interpretation of the question :)

Regards,

Patrick
GRayL

You know me Patrick - I never assume anything:-/
Cristal638

ASKER
Actually the example is the result I'm looking for.

July Shift minus Jun Shift equals NewShift

31-Jul-06      9113      0.278805355
30-Jun-06      9113      -2.23096039
            
            2.509765745 = NewShift
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cristal638

ASKER
Hi Patrick,
Unfortunately -sum gives me the wrong result.  Arg...

I'm checking Pete's to see if it gives me what i'm looking for.
As always, thank you all for your help...
GRayL

31-Jul-06     9113     0.278805355
30-Jun-06     9113     -2.23096039

but where do those humbers come from?
GRayL

humbers=numbers!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Cristal638

ASKER
We have a winner...  Thank you all, but Pete gave me exactly what I was looking for...

Thanks again,
Dave
Patrick Matthews

Cristal638,

SELECT DISTINCT t1.ID,
    ((SELECT -t2.Shift
    FROM SomeTable AS t2
    WHERE t2.ID = t1.ID And t2.AsOf = #30 Jun 2006#) +
    (SELECT -t3.Shift
    FROM SomeTable AS t3
    WHERE t3.ID = t1.ID And t3.AsOf = #31 Jul 2006#)) AS NewShift
FROM SomeTable AS t1
ORDER BY t1.ID

Regards,

Patrick