CODEWITHSUNDEEP
sqlvalidationms-access
Testers Logic
Testers Logic

Reputation: 79

MS Access Validation - All the values in Column 2 should be the same according the distinct values in Column 1

I can better explain with an example:

I need to verify a table whether the distinct values from 'ProdID' column (say 713141535) has the same set of values in 'AccountNo' column (say 2), i.e., Prodid with '713141535' has the same AccountNo which is '2','2','2'. (Refer image from below link)

But the prodid '855325150' has different AccountNo which is 5,4,5. All the account numbers are not the same according to ProdID, so the validation is failed.

Could you please help me to write a query to validate this type of scenario using MS Access.

Note: I get new tables for each release, so I am unaware of the values in table as it differs every time.

Thanks

Basha

http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/ms-access-validation-all-the-values-in-column-2/10d957ef-94f7-48ff-b708-277dbb01d077?tm=1405338095580

Upvotes: 0

Views: 53

Answers (1)

Mark C.
Mark C.

Reputation: 6460

You can use DISTINCT to find the mismatches between ProdID and AccountNo and use that recordset as your subquery.

SELECT Sub.ProdID, Count(Sub.ProdID) AS CountOfProdID
FROM (SELECT DISTINCT ProdID, AccountNo
FROM Prod
)  AS Sub
GROUP BY Sub.ProdID
HAVING Count(Sub.ProdID) > 1

Note: I used Prod as my table name. Sub is the alias given to the subquery.

This query will return the ProdID that has mismatches in AccountNo

Upvotes: 1

Related Questions