Dataset - DataTable Strategy for Export CSV

[themaj]

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.

Quick explanation/example:

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
etc.
etc.

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

[G_Waddell]

Unless the 4 tables hold the same fields (or at least fields you could map to each other,) then I doubt you would be able to do it as a single query...

But if you are trying to get them all in to one csv then you must be able to?

You will have to use nested sub queries or Unions but you must ensure that each query returns the Same fields so you can produce the results as a whole.

[kRod]

Have you thought of using an XML file instead of the CSV. You could take advantage of the DataSet.Read and DataSet.Write methods.

[themaj]

@ G Waddell - yes on the mapped fields; the xType (1,2,3,4) indicates what kind of transaction and the ID points to the record number of that Type. So for example:
Date, Store, xType, ID, $50
20120705, 2, 1, 3, 50 'translates to July 5, 2012, Shanghai, Payment, Visa, $50
20120705, 2, 1,1,75 'translates to July 5, 2012, Shanghai, Payment, Cash, $50
20120705, 2, 2, 1, 50 'translates to July 5, 2012, Shanghai, Comp, Owner Meal, $50
20120705, 2, 3, 1, 50 'translates to July 5, 2012, Shanghai, Promo, Mexican Fiesta, $50

If I coded it straight away it would contain an If-Then that builds/rebuilds the query line by line of the transaction file; with an average of 1500 line of transaction everyday it's feasible but there has to be a faster, more efficient way. I'm curious about the UNION but don't have enough knowledge or experience. A simple sample would help clear it up if possible. The attached mapping image might help.

@kRod - I really don't know enough about XML at this point but the requirement I'm working with dictates that the export file is CSV. I'm certainly interested in learning other ways...that's why I'm here.
I've already been able to read all the DBF's into a single Dataset with 4 DataTables through a DataAdapter, now it's a question of how to bring it all together.....I think :(

Appreciate your suggestions

[themaj]

I'm trying to work on this UNION thing but just don't get it. I've attached a RAR of a simplistic sample of the db tables in Access.

Included are:
4 static table (tenders, promos, comps & stores)
1 dynamic table of Transactions
3 queries that emulate what I'm trying to do.

The 3 queries are exactly the same other then the fact that the 'xType' links the Transactions to one of the 3 xType tables.

Can you take a look and suggest how I can get to one export file of transactions that mimics the Transaction table but adds in the text description for the xType. If I can just get the query right (or a different strategy), I'll figure out how to export to CSV.

I'll be happy to do this in either VB.NET or Access if possible. I'm here to learn.

Cheers & thanks for any assistance you can provide.