Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

I need to total negative numbers and positive numbers from the same field in a file using WRKQRY

Posted on 2006-10-24
7
Medium Priority
?
627 Views
Last Modified: 2012-08-13
I have GL entries in a file using a field GLAMT.  GLAMT may contain a negative or positive number for each record stored in the file. Is there a way using 'Define Result Fields' to create total fields based on whether the amount field data is positive or negative.
I.E. create a field called DEBITS totaling all amounts that are positive in the GLAMT field. create a field called CREDITS totalling all amounts that are negative in the GLAMT field.

I need this pretty quick.

Thanks,

Phil
0
Comment
Question by:pipster1
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Accepted Solution

by:
_b_h earned 2000 total points
ID: 17798353
Hi Phil

Create query 1 to total the amounts where GLAMT is less than 0, output to a file outfile1
Create query 2 to total the amounts where GLAMT is not less than 0, output to a file outfile2
Create query 3 to join outfile1 and outfile2 together

You might be able to join the file to itself; I will try to test this method later to confirm that it works.
Join the file to itself as T01.file and T02.file
Select records T01.GLAMT *LT 0 OR T02.GLAMT *GE 0
then summarize on T01.GLAMT and T02.GLAMT

Post back any questions
Barry
0
 

Author Comment

by:pipster1
ID: 17798453
Sounds cool on the join to itself, I'll try that first and let u know
0
 
LVL 13

Expert Comment

by:_b_h
ID: 17798474
Phil
Found a system.
Second method does NOT work. I think the select would have to be done before the join to make it work.
Barry
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:pipster1
ID: 17798525
I guess there's no way to determine if a field is negative or not in result fields.
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17799022
hi
try
select sum(abs(GLAMT)) from myfile

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17799090
Sorry just read the question.
Barry is almost there.
2 queries, the first select qty > 0 and simply creates the work field from GLAMT
, the second selects <=0 but multiplies GLAMT by -1 to make it +ve
the third now accumulates the values.
0
 

Author Comment

by:pipster1
ID: 17799210
You're right Dave, Barry's is the one.

Thanks to you both.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to kick start your career in 2018? Add app developer skills to your resume. January’s Course of the Month features Android App Development training with hands-on learning.  Read on to learn why these skills are important.
The article explains the process to deploy a Self-Service password reset portal I developed a few years ago. Hopefully, it will prove useful to someone.  Any comments, bug reports etc. are welcome...
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question