table level validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table named Bank transaction
field deposit (currency)
" withdraw(currency)
i want to have the validation @ the table properties like
sum(withdraw) > sum(deposit) validation text ( withdraw can't exceed deposit)
table 2 named total earnings
field financialyear (text)
" total earnings (currency)
TABLE LEVEL validation on TABLE BANKTRANSACTION
SUM(table!totalearnings.totalearnings) < (sum(total deposit))
valtext(how the total deposit exceed the earnings)
OR
Kindly advise an alternative error check on the table level or at form
level please
BALU
india
 
I have a table named Bank transaction
field deposit (currency)
" withdraw(currency)
i want to have the validation @ the table properties like
sum(withdraw) > sum(deposit) validation text ( withdraw can't exceed deposit)
table 2 named total earnings
field financialyear (text)
" total earnings (currency)
TABLE LEVEL validation on TABLE BANKTRANSACTION
SUM(table!totalearnings.totalearnings) < (sum(total deposit))
valtext(how the total deposit exceed the earnings)
Click to expand...

Sub AlterTables()
With CurrentProject.Connection

.Execute _
"ALTER TABLE BankTransaction ADD CONSTRAINT" & _
" withdraw_cant_exceed_deposit" & _
" CHECK (NOT EXISTS (" & _
" SELECT B.CustomerID" & _
" FROM BankTransaction AS B" & _
" GROUP BY B.CustomerID" & _
" HAVING SUM(B.withdraw) > SUM(B.deposit)));"

.Execute _
"ALTER TABLE BankTransaction ADD CONSTRAINT" & _
" total_deposit_cant_exceed_earnings" & _
" CHECK (NOT EXISTS (" & _
" SELECT B.CustomerID" & _
" FROM BankTransaction AS B INNER JOIN totalearnings AS E" & _
" ON B.CustomerID = E.CustomerID" & _
" GROUP BY B.CustomerID" & _
" HAVING SUM(B.deposit) > SUM(E.totalearnings)));"

End With
End Sub
 
Back
Top