MS Access SQL updating in sequence -

MS Access SQL updating in sequence -

i have table provides point-in-time snapshot next headings:

| cust# | lastly trans. | charge | quantity |

every month, receive file 3rd party transactions add together new cust# or alter existing client information. having problems when there multiple updates same cust# in 1 month.

for example:

processing next transaction file:

should yield next snapshot table:

it may not best method, have 3 separate queries handle new, alter , cancel. there no problems new , cancel.

here's how alter query set up:

update snp inner bring together tr on snp.[cust#] = tr.[cust#] set snp.[last trans] = tr.transaction, snp.charge = snp.charge + tr.charge, snp.quantity = tr.quantity tr.trans='change'

note charge incremental , quantity not. updating charge working expected, quantity not. not latest quantity.

how ensure if there changes 1 customer, lastly quantity field taken latest alter row (ie. max id of cust#)?

select * snp id in (select max(id) tr grouping cust#)

the inner query give customers' max id. can filter cust# based on alter criteria. outer query give details of row. can utilize values in queries.

sql ms-access