Hi,
I am trying to create a database for converting currency settlement instructions from one format into another. Just a little background information; At work I use two different mainframe systems to add settlement instructions to. Unfortunatley the format for these same instructions is different in each system. Here is just an example of what I mean, it isn't exact but just illustrates the differences.
Ex. Mainframe 1 format:
Agent ID
/AC Number
Custodian ID
Ex. Mainframe 2 format:
AC Number
Custodian ID
Agent ID
I receive a weekly exception report that shows instructions that have failed to feed from one system to the other because of the formatting being incorrect. Currently, I have to take these instructions from the one system and manually change them so that they match the format for the 2nd system. This is formatted in excel so that it can be bulk uploaded into the mainframe. We have several thousand instructions that need changing, so I would like to create this in Access 2007, instead of Excel. I would like to have a database where I can load the report I receive into the database, run a query to convert the instructions into the correct format for the 2nd mainframe and possibly be able to export the query results into an excel spreadsheet. I still have to get more details on the information I need, but without getting into too much details, is this something that is possible to do using a query? Any rough ideas what this might look like in Access with the limited details I have provided so far?