Trying to figure out the best way to do create a CSV export file from my source files and need some input.
I'm trying to build a CSV export file for import to an Accounting System where there are 3 different tables mapped to a single field in the main table.
All tables are DBF and the structure is fixed by the Vendor & there will be no updating needed after CSV is created.
The main table holds 'Transactions' and one of the fields 'xType' holds an integer. Let's say 1 through 4.
1 refers to a Payment Type
2 refers to a Comp Type
3 refers to a Promo Type
4 refers to a Void Type
Another field in the Transaction table refers to the ID of a record in the corresponding table (Payment, Comps, Promo, Voids) indicated by the xType.
I want to query the main table and link the xtype to the appropriate tables (Payment, Comps, Promo, Voids) and return the Name of the corresponding item
If xType = 1 then use the Payment table
If xType = 2 then use the Comp table
My initial thought is to create 4 DataTables holding the 4 DBF tables within a DataSet and then have 3 separate queries to pull the records out of the Transaction and link them with the corresponding xType tables. Then write the resulting records to another Datatable to be exported.
Can this be done in a single query?
Any ideas or suggestions? I'm not really looking for the coding methods yet; I'm trying the figure out the best strategy to accomplish the end result....one CSV export table.
Appreciate any help on this, thanks