Sherman6789
Programmer
We have data packets delivered daily in the office. Some of the information is entered into a DB table if it has been assigned a main ABC-ID number by headquarters. Some records are merged directly into the DB from an Excel Spreadsheet sent by headquarters. The ABC-ID number is already unique and there is no problem with identification.
However, we have another group of packets that come into the office with most data but they have not assigned an ABC-ID number. Of course, this number must be unique. In the past, we shelved these packets in another room until they were assigned an ABC-ID then we followed the regular procedures.
The problem now is that the number of packets without ABC-ID is growing too fast and we need the information placed into the database immediately for searching, even if we have to give them a temporary number and change to the permanent number after they are assigned ABC-ID numbers. Besides that, the bosses are tired of this bid pile of un-assigned packets taking up space.
Here is what I think may be a solution.
*****************************************
Make a copy of the DB structure from table #1, remove the key from the ABC-ID field and setup an auto number as the Temp-ID field for table #2. Enter the data into table #2 for those without ABC-ID numbers as they come in. Whenever a packet is assigned an ABC-ID number; we update the record and add it in the proper field. We then periodically run a small program that searches table #2 for ABC-ID numbers that are no longer empty. When one is found, it is marked and transfered to Table #1 and the item in Table #2 is then deleted. If for some reason, the ABC-ID number is found in Table #1, only certain fields are merged into Table #1. The item in Table #2 is still deleted. If possible, a report can be made of the update.
I hope that this is clear. I am ready to try to answer questions. If this is a reasonable solution, please tell me the steps and code to do what I propose. If you can think of a simpler or easier method, please let me know.
Thanks!
sherman6789
However, we have another group of packets that come into the office with most data but they have not assigned an ABC-ID number. Of course, this number must be unique. In the past, we shelved these packets in another room until they were assigned an ABC-ID then we followed the regular procedures.
The problem now is that the number of packets without ABC-ID is growing too fast and we need the information placed into the database immediately for searching, even if we have to give them a temporary number and change to the permanent number after they are assigned ABC-ID numbers. Besides that, the bosses are tired of this bid pile of un-assigned packets taking up space.
Here is what I think may be a solution.
*****************************************
Make a copy of the DB structure from table #1, remove the key from the ABC-ID field and setup an auto number as the Temp-ID field for table #2. Enter the data into table #2 for those without ABC-ID numbers as they come in. Whenever a packet is assigned an ABC-ID number; we update the record and add it in the proper field. We then periodically run a small program that searches table #2 for ABC-ID numbers that are no longer empty. When one is found, it is marked and transfered to Table #1 and the item in Table #2 is then deleted. If for some reason, the ABC-ID number is found in Table #1, only certain fields are merged into Table #1. The item in Table #2 is still deleted. If possible, a report can be made of the update.
I hope that this is clear. I am ready to try to answer questions. If this is a reasonable solution, please tell me the steps and code to do what I propose. If you can think of a simpler or easier method, please let me know.
Thanks!
sherman6789
