Question about filtering / SQL for a table

[LudovicV]

Hello there, I'm new here so I hope I post it on the right place



I have this table :




As you can see, the sum of the column "Difference" for the entry "JOHN DOE" equals 0 (for example).

I would like to filter this table so that "JOHN DOE" does not appear on the table.

Is it possible with a filter or a SQL query ?

Thank you in advance and best wishes.

LudovicV

[ranman256]

[identity]<>"john doe"

[LudovicV]

[identity]<>"john doe"

Sorry I wasn't very precise : I'd like to delete entries with the sum = 0 and the entries are often changing.

The ending result should be that :



Thank you in advance for your help.

[Welshgasman]

SELECT Transactions.Description, Sum(Transactions.Amount) AS SumOfAmount
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Sum(Transactions.Amount))<>0));

Adjust your fieldnames to suit.

[LudovicV]

SELECT Transactions.Description, Sum(Transactions.Amount) AS SumOfAmount
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Sum(Transactions.Amount))<>0));

Adjust your fieldnames to suit.

Ok it did work but I lost the other columns ("Account", "Record", etc.) and I want to keep them, is it possible ?

Thank you

[Welshgasman]

Ok it did work but I lost the other columns ("Account", "Record", etc.) and I want to keep them, is it possible ?

Thank you

You select those fields as well if you need them. As they are the same (well at least in the picture you showed) then the grouping will still work.
I was only showing the logic.

[LudovicV]

Yeah I can see the logic but OK forget about the "Account" column, the others are different, is there a possibility to keep them (with a JOIN command maybe) ?

[Welshgasman]

Yeah I can see the logic but OK forget about the "Account" column, the others are different, is there a possibility to keep them (with a JOIN command maybe) ?

No.
I would identify a field that you can refer to.
Put that in the groupby query.
Then use that query as a sub query where that field is NOT IN that query.

There might be a one query way, but I do not know of it.

[Micron]

Deleting records often turns out to be a bad idea. Also, what you have there is a calculated table field - also often a bad idea. Getting off of my soap box now...

Methinks if in a Totals query you group by Identity, have SumOfDebit, SumOfCredit, subtract the 2 in another query field and set the criteria as = 0 for that field. Or if you only want to see one particular Identity, add that criteria. Untested and from my coffee starved brain...