-
September 9th, 2012, 08:57 AM
#1
[RESOLVED] SQL Query Problem
I have an SQL Query which lists records from a Table which contains Invoices and Credits.
The problem is that the Values are Positive for both Invoices and Credits - Just that the Document Type Shows which is which.
My problem is that I want to sum the Total Value by ADDING Invoice Vales and SUBTRACTING Credit values.
Is it possible to somehow change the Amount to be Negative in an SQL Statement (Based on a Column value containing the word "Credit")
This is my current query
Code:
Select [Territory] , [OBatch], [DDocType], [DDocNo], [DDocDate], [Account], [Company], [Value] from [OrderArchiveIndex] ORDER BY [Territory] ASC , [Account] ASC
DDocType contains "Invoice" or "Credit"
Value contains a Positive Value always - would like this to become Negative if DDOCType = "Credit"
Thanks for any ideas
PS - Found the Code Tags - they dont seem to be part of "QUICK REPLY"
-
September 9th, 2012, 09:22 AM
#2
Re: SQL Query Problem
You could use a "Case When" in the "Select" to ask for the record type
and use a "-" (minus sign) before the field to convert to negative
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
September 9th, 2012, 09:27 PM
#3
Re: SQL Query Problem
I have tried making this work using Case but keep getting syntax errors
Does Case work with an SQL Statement on an Access database or do you need to be running T-SQL (with a SQL Server Database) ?
I will persevere in the mean time,
Thanks
-
September 9th, 2012, 11:33 PM
#4
Re: SQL Query Problem
With MS Access you could use IIF
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
September 10th, 2012, 01:36 AM
#5
Re: SQL Query Problem
Thanks for the guidance - the IIF Statement worked perfectly
Here is the final Query which works (Thanks)
Select [Salesman], [Account], [Company], [DDocType], [DDocNo], [DDocDate], [DDocStatus], [DDocOPID], [OOrderV7], [OOrderV9], IIF( [DDoctype] = 'Credit', [OOrderV12] * -1, [OOrderV12]) as InvoiceTotal from [OrderArchiveIndex] ORDER BY [DDocNo] ASC
-
September 10th, 2012, 01:37 AM
#6
Re: SQL Query Problem
Thanks for the guidance - the IIF Statement worked perfectly
Here is the final Query which works (Thanks)
Code:
Select [Salesman], [Account], [Company], [DDocType], [DDocNo], [DDocDate], [DDocStatus], [DDocOPID], [OOrderV7], [OOrderV9], IIF( [DDoctype] = 'Credit', [OOrderV12] * -1, [OOrderV12]) as InvoiceTotal from [OrderArchiveIndex] ORDER BY [DDocNo] ASC
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|