ms access - Sql multiple reservations and duplicate vallues -

ms access - Sql multiple reservations and duplicate vallues -

lets assume have table named reserations( pk, clientid, order_number, credit_card ). client can create multiple reservations reservation considered finish when credit_card not null ( meaning client has prepaid order ) visa, mastercard,etc..

so have table total of reservations , want clean next rules: if client has made valid reservation ( credit card not null ) if client has other reservations not paid ( credit card null ) auto reserve them him( meaning add together credit card type in credit_card columns null specific client ). otherwise client not valid( not single valid credit card ) records deleted.

how implement in sql microsoft access? thoughts : select table, remove duplicates , update records. problem here how know if there @ to the lowest degree 1 valid reservation made? maybe utilize count function count valid reservations each client ?

if understanding correct, have utilize next queries this. first delete reservations client has not made reservation (credit_card null), using next query

delete reserations reserations r clientid not in (select clientid reserations credit_card not null)

then update reservation credit card null, credit card available paid reservations, using next query

update reserations set credid_card = (select top(1) credit_card reserations r1 r1.clientid = r.clientid , r1.credid_card not null) reserations r credid_card null

sql ms-access