CODEWITHSUNDEEP
ms-access
Jack Diamond
Jack Diamond

Reputation: 62

Access: Find records where duplicate values in one field and contradicting values in another are found

So I have a lot of data which is stock adjustments, some are gains and some are losses.

I need to find all the records which net to £0.

They are from multiple locations.

I have data like this:

Store Code     Product Code.    Adjust Cost
AA1                  12345.             £20
AA1                  12345.            -£20
AA1.                 12345.             £40
AAB                  45567.             £14
AAB                  43256.            -£14

I also have a field which concats Store Code and Product Code to create a unique value to each store that has adjusted that code.

From the data above I will be looking to select only these records

AA1                  12345.                £20
AA1                  12345.               -£20

Thanks in advance for any help.

Upvotes: 0

Views: 39

Answers (1)

Erik A
Erik A

Reputation: 32682

This should do the trick:

SELECT * FROM MyTable t
WHERE (SELECT Sum(i.[Adjust Cost]) FROM MyTable i WHERE i.[Store Code] = t.[Store Code]) = 0

Upvotes: 1

Related Questions