Database Query - Need help

[krymer]

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?

[edakhi]

It's quite important to know if the diferences among the fields are only order or if they have specials characters, in the query you can order the fields and using the code generator, (click right button mouse in the field need, lookup en each row), you can add the special character as a string, using & "/", for example. I hope it is a little help.