Reformatting data fron one table to a new table (1 Viewer)

[reglarh]

Sounds simple but...........

I have an existing database with a membership table consisting of about 40 fields. I want to transfer this data to a new system using Excel as the interchange mechanism.

The corresponding table in the new system (not Access based) consists of about 70 fields. Some of these fields are common in format. Some do not exist on my system and need to be populated with assumed values (Yes/No, Null etc.) and some need manipulation from the old to the new formats e.g. a Yes/No field will need expanding to a text value and concatenated with similar Yes/No fields into a single Memo field.

Obviously quite a lot of VBA code is necessary to achieve this but the issue I don't understand is how to easily create an INSERT INTO statement from some 70 fields where the source data for each field is one of:

• Direct copy form source table;
• A value derived from the source data;
• A constant value.

Any help appreciated!

 

[plog]

I don't understand is how to easily create an INSERT INTO statement...

Huh? Doesn't the new system have import capabilities? Spit your existing data out to Excel, then import that Excel file into the new system?

Also, 40 fields sounds like an improperly structured table, and you are moving to a table with 70 fields?

 

[Ranman256]

begin a select query,
drag the fields from the source table and put on query line
add any calculated fields ,and constants , ie:
MyState: OH
or
Speed: [dist]/[time]


now you can export this query to excel using:
vFile = "c:\folder\myFile.xlsx"
vQry = "qsExport2Excel"

docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true

 

[reglarh]

In response to the two replies above.......

No it's is not a badly designed table, just big! A lot of the fields are user selectable options that guide the processing carried out within the system.

Yes the system does have import facilities but the codification of data is substantially different and in some case quite complex. The conversion processes needed are beyond what is achievable in a query, hence the need to process the data with VBA.

 

[ZoeMaKayla]

Please I have a MS Access Database with an invoice that and the query that calculate the total order on invoice. Also, I have another table for Payment Details but I need to pick the total for order on Invoice to a field for Total on Payment Details. Can any one please help with this