But when I run the query it does not come up with any data, I think this may be because there is no data in the second table.
Do a Left Join from the main table with data to the other, and it will return all the records from the main data table.
I will also need to concatenate fields, for example in the input file there are 3 address lines, but it in the output file we need to concatenate address lines 1 and 2 with a pipe in between.
It is easy to concatenate fields in a calculated field in your query, i.e.
Code:
Address: [Address Line1] & "|" & [Address Line2]
The end result is to have a form which has a button to input a selected file, press process which runs the query and another button which then outputs the file to go into the CRM.
You can create Macros which do most of these steps, and run these macros from your buttons. If you need to make it more dynamic, you can start with a Macro, and convert those Macros to Visual Basic (button on the Macro menu). This gives you the "basic" code you need, and you can modify it to make it more dynamic.
Note: Unless you have an Action Query (i.e. Append, Make-Table, or Delete Query), you don't actually have to "run" the query. With a select query, it is real-time as you open, reference, export it. So all you need to do is export the Select Query.