CODEWITHSUNDEEP
ms-access
WantToLearnNewSkills
WantToLearnNewSkills

Reputation: 51

Microsoft Access - How to perform validation check and produce error message

I would like to produce an error message when the (sumofqtyreturn + sumofqtyissued) is greater than sumofqtyordered. I want to carry out this validation check so that when the user enters a value(qtyreturn or qtyissued) which is greater than the balance, the system should produce an error. Can this be done?

This is the sql view for my query:

SELECT i.itemNo, Nz(TotalOrdered,0), Nz(TotalReturned,0), Nz(TotalIssued,0), 
     (Nz(TotalOrdered,0)-Nz(TotalIssued,0)+Nz(TotalReturned,0)) AS Balance

FROM ((item AS i 

LEFT JOIN (SELECT itemno, Sum(qtyordered) AS TotalOrdered 

FROM delivered_item 

GROUP BY itemno)  AS d ON d.itemno=i.itemno)

LEFT JOIN (SELECT itemno, Sum(qtyreturn) AS TotalReturned FROM item_return 

GROUP BY itemno)  AS r ON r.itemno=i.itemno)

LEFT JOIN (SELECT itemno, Sum(qtyissued) AS TotalIssued FROM item_issued 

GROUP BY itemno)  AS iss ON iss.itemno=i.itemno

or any1 has other good idea of how to inform/warn the user something is wrong about the qtyissued/qtyreturn?(value too large)

Upvotes: 0

Views: 57

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

Here is the general idea.

SELECT q.AText, 
       q.SumOfANumber, 
       q.SumOfADecimal, 
       IIf([SumOfANumber]-[SumOfADecimal]<0,"Problem!","Yes") AS IsOkay
FROM (SELECT t.AText, 
            Sum(t.ANumber) AS SumOfANumber, 
            Sum(t.ADecimal) AS SumOfADecimal
      FROM Table1 As t
      GROUP BY t.AText)  AS q;

The aggregate query becomes a subquery and the relevant fields are compared in the main query. I have used aliases for Table1, so you have Table1 As t, and for the subquery.

Upvotes: 2

Related Questions