vangogh228
Registered User.
- Local time
- Today, 10:10
- Joined
- Apr 19, 2002
- Messages
- 302
I have a data table for open accounts receivable collections activity that has the following important fields:
Autonum (Key)
Code
InvoiceNumber
Amount
Date
Each day, this table will be updated with new invoices. However, a significant number of updates are required.
For example, under certain circumstances the original invoice will be voided and a replacement number will be issued for a different amount. In that case, the Code field = 200 and the new invoice number will be the same as the one it replaces, but with an "A" at the end... then "B" if it goes through that cycle again, etc. So, Invoice 12345678 would be replaced with 12345678A.
But... by the time that happens, some work will have been done, perhaps including collection call notes, so I don't want to delete the old record. What I want to do is take all the "200" Code records, use the first 8 digits of the invoice number to match the new to the old, and update only the Code, InvoiceNumber and Amount from the new to the old, leaving the original invoice date.
I've done some simple update queries, but I can't figure out the logic on this. Currently, we're doing a manual import each morning of data we're querying from our mainframe system. So, if I can get through this issue, my next question will be about how to automate that process!!
THANKS so much for any help.
Tom
Autonum (Key)
Code
InvoiceNumber
Amount
Date
Each day, this table will be updated with new invoices. However, a significant number of updates are required.
For example, under certain circumstances the original invoice will be voided and a replacement number will be issued for a different amount. In that case, the Code field = 200 and the new invoice number will be the same as the one it replaces, but with an "A" at the end... then "B" if it goes through that cycle again, etc. So, Invoice 12345678 would be replaced with 12345678A.
But... by the time that happens, some work will have been done, perhaps including collection call notes, so I don't want to delete the old record. What I want to do is take all the "200" Code records, use the first 8 digits of the invoice number to match the new to the old, and update only the Code, InvoiceNumber and Amount from the new to the old, leaving the original invoice date.
I've done some simple update queries, but I can't figure out the logic on this. Currently, we're doing a manual import each morning of data we're querying from our mainframe system. So, if I can get through this issue, my next question will be about how to automate that process!!
THANKS so much for any help.
Tom
