Access query follow up to question that was answered

tfkcon
tfkcon used Ask the Experts™
on
Here is the questions:
I have an Access db with a sample file containing invoices paid.  
I need to write a report that groups payment of equal amounts (Use GrossAmt) for each vendor together.

Ignore the sign (positive or negative) of the amount. Ignore groups with only one record.
For each vendor show a group header containing the vendor number and name.

For example if the GrossAmt is $100 in one record and the GrossAmt is $-100 in another record treat that as a match.

Here is the answer that I wanted some clarification on the sql:
SELECT ApTest.VenNum, Abs(ApTest.GrossAmt) AS [Gross Amount Duplicate], ApVenTest.VenName, ApTest.InvNum AS InvNum, ApTest.DueDte, ApTest.PaidDte
FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum = ApVenTest.VenNum
GROUP BY ApTest.VenNum, ApTest.GrossAmt, ApVenTest.VenName, ApTest.InvNum, ApTest.DueDte, ApTest.PaidDte
HAVING (((ApTest.VenNum) In (SELECT [VenNum] FROM [ApTest] As Tmp GROUP BY [VenNum], Abs([GrossAmt]) HAVING Count(*)>1  And Abs([GrossAmt]) = Abs([ApTest].[GrossAmt]))))
ORDER BY ApTest.VenNum, Abs(ApTest.GrossAmt);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
the Abs() function converts (- values) to + values.. is that what you want to know?

Author

Commented:
Im more concerned with the part of the query where you are using the "IN"

Author

Commented:
Or if you could just explain your Having Clause...Im a little confused how you came up with it.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Top Expert 2016

Commented:
that part...(in short)

it is looking for [VenNum] that is having a count >1, meaning duplicate or more
with (also) duplicate (same) [GrossAmt]

Author

Commented:
Im gonna give you the points but could that have been done in the first Having clause?
Top Expert 2016
Commented:
no, the second part- " In( select ... ) " is important here or else you will not get the correct number of records returned by the query.
Top Expert 2016

Commented:
hope you understood the explanation.. i am not good at picking the correct word to explain things.. sorry ( english is not my first language) :-(

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial