Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Query to look for accounts with both positive and negative values

Posted on 2014-03-21
Medium Priority
305 Views
I have a table (tblInvoices) that has three (relevant) fields:

- AccountNumber
- InvoiceNumber
- InvoiceAmount

Some of the invoice amounts are positive and some are negative.

I'm looking for a query that will show all the accounts that have both positive and negative invoice amounts (i.e. an account that has at least one invoice that is positive and at least one invoice that is negative).

It doesn't matter to me if the query results show just one line for each matching AccountNumber of if the query results show all the invoices for each matching AccountNumber.

0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2
• 2

LVL 7

Expert Comment

ID: 39945729
sorry, needs more work
0

LVL 70

Expert Comment

ID: 39945747
Change the code below to ">= 0" if by positive you mean "0 or more" rather than "more than 0":

SELECT
AccountNumber,
SUM(CASE WHEN InvoiceAmount > 0 THEN 1 ELSE 0 END) AS Positive_Invoice_Count,
SUM(CASE WHEN InvoiceAmount < 0 THEN 1 ELSE 0 END) AS Negative_Invoice_Count
FROM tblInvoices
GROUP BY
AccountNumber
HAVING
SUM(CASE WHEN InvoiceAmount > 0 THEN 1 ELSE 0 END) >= 1 AND
SUM(CASE WHEN InvoiceAmount < 0 THEN 1 ELSE 0 END) >= 1
0

LVL 7

Expert Comment

ID: 39945779
or
SELECT DISTINCT Table1.AccountNumber, Table1.InvoiceNumber, Table1.InvoiceAmount
FROM Table1
WHERE (((Table1.InvoiceAmount)>0)) OR (((Table1.InvoiceAmount)<=0));
0

Author Comment

ID: 39945783
This works:

SELECT tblInvoices.AccountNumber
FROM tblInvoices
WHERE tblInvoices.InvoiceAmount>0 And
tblInvoices.AccountNumber IN (SELECT tblInvoices.AccountNumber FROM tblInvoices WHERE tblInvoices.InvoiceAmount<0)
ORDER BY tblInvoices.AccountNumber;
0

Author Comment

ID: 39945994
I've requested that this question be closed as follows:

for the following reason:

Shortly after posting my question, I realized how to make the query.
0

LVL 70

Expert Comment

ID: 39945995
Could we see the answer?  That would add the solution to the EE db, and allow us to confirm that it was independent of the answers provided here.
0

Author Comment

ID: 39946183
I posted the answer above and accepted it as the solution.
0

Accepted Solution

ID: 39946185
In order to avoid confusion, here it is again:

SELECT tblInvoices.AccountNumber
FROM tblInvoices
WHERE tblInvoices.InvoiceAmount>0 And
tblInvoices.AccountNumber IN (SELECT tblInvoices.AccountNumber FROM tblInvoices WHERE tblInvoices.InvoiceAmount<0)
ORDER BY tblInvoices.AccountNumber;
0

Featured Post

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if youâ€™re going to set referential integrity: Decide if you want cascade updaâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
Suggested Courses
Course of the Month4 days, 10 hours left to enroll