How do I create this update statement?
Posted on 2009-04-28
I have 3 tables with the follow fields.
SIN (Social Insurance Number), Primary Key.
On the Clients table, the office field is currently null. This is the field I need to update.
It is possible that clients have multiple Disbursements and multiple AR Invoices.
I need to update the clients (office) field with the office value of whatever table has the most recent date (CKdate or Invoice_Date).
I would attempt to do this in 3 steps.
1. Figure out the script to get the latest office (according to CKDATE) from the Disbursements.
2. Figure out the script to get the latest office (according to Invoice_Date) from tblARInvoices.
3. Combine 1 + 2 in an update statement to solve the issue.
Any help would be greatly appreciated.