I am not the person that handles the raw data load, so there is no way for me to convert to a text file
You wouldn't have to, the VBA code could! You could make it part of the import process. A well-designed Access database is all Form driven, so you would just have a Form with a button where they select which file they want to import, and click the button, and the VBA code handles all the rest!
I apologize I am very new to access. Could I take the raw data table (automatically loaded multiple times a day into access) and create a "new" table that would have the split up data? That way I don't have to "touch" the raw data I could just work with the "new" table. Since raw data is loaded multiple times a day, I don't want to overwrite, I would need to append to existing "new" table....
You have taken on quite a task for a newbie to Access. Do you have any relational database or programming background?
You could do those things, however, they might get a little tricky.
Who is importing the data and how are they doing it?
Is there a limit to how many different fields there might be (in your example, some had 5 and others had 6)?
I am thinking there are a few ways to approach this (if you want to use this approach), such as:
Option 1
- Have the person import into an existing temporary table
- Create an Append Query that runs on that temporary table, splitting up the fields as needed, and then writes the records to the final table
- Create a Macro that runs the Append Query you just created, and then some code to delete the data out of that temporary table (so it is blank again and ready for the next run)
So the person would just need to run this macro after importing the file into the temporary table
Option 2
- Have the person import into an existing temporary table
- Create VBA code which looks through the whole table, and writes the record to the new table, and then delete the table from the temporary table
I am thinking that Option 1 might be the option you want to go. You would just need to create a bunch of calculated fields that extra each value. And you should give them a name (alias) that matches field name in your final table.