Hey folks,
I have about 30 different master tables and there are about 100 individual data files that need to be inserted into each master.
So far, I have created a macro in excel to list all of the possible field names in the data files, and I have dervived my my master tables from the output.
So, why did I need to do this? Here's the unfortunate twist. The individual data tables all list only the fields that contain data in alphabetical order. There are approximately 40 fields total across all of the data files, but each file usually only contains about 25 fields. Don't ask me why they are formatted this way, I can't figure out the logic either.
My issue is that I now have to get the data from these 1200 tables individual tables and organize them into their respective master.
So, I imagine I have two methods of doing this. I can either clean up the data in Excel trying to create some kind of standard template that I can mass upload using an import spec, but I would prefer to do all of the legwork in Access.
Has anyone ever attempted this, or know of a method by which to do this?
Finally, to throw another curve-ball your way there are several fields that need to be changed from "Text" to "Memo" as they contain more than 255 characters.
Any help/ideas about how to approach this would be appreciated.
Thanks for reading!
I have about 30 different master tables and there are about 100 individual data files that need to be inserted into each master.
So far, I have created a macro in excel to list all of the possible field names in the data files, and I have dervived my my master tables from the output.
So, why did I need to do this? Here's the unfortunate twist. The individual data tables all list only the fields that contain data in alphabetical order. There are approximately 40 fields total across all of the data files, but each file usually only contains about 25 fields. Don't ask me why they are formatted this way, I can't figure out the logic either.
My issue is that I now have to get the data from these 1200 tables individual tables and organize them into their respective master.
So, I imagine I have two methods of doing this. I can either clean up the data in Excel trying to create some kind of standard template that I can mass upload using an import spec, but I would prefer to do all of the legwork in Access.
Has anyone ever attempted this, or know of a method by which to do this?
Finally, to throw another curve-ball your way there are several fields that need to be changed from "Text" to "Memo" as they contain more than 255 characters.
Any help/ideas about how to approach this would be appreciated.
Thanks for reading!