Make Table Query automatically splitting data into separate tables

[AWM]

Hi everyone...

I've created code to automatically pull a data table from our ERP
system (through an ODBC connection). The data table consists of part
detail information that serves as the basis for our forecasting/budget
process. The data table is quite large (approx. 2000 rows by 30
columns) and is not normalized.

The table column names do not change therefore I know what columns to
break-up in order to normalize the table(s). Therefore I would like to
automatically, everytime the data table is pulled from the ERP system,
normalize the table by breaking it up into a few tables. I would like
to do this automatically through a "make table query" (or similar
process), however I cannot find any information that tells me whether
this possible.

Any help is appreciated.

Thanks.

 

[John Vinson]

Hi everyone...

I've created code to automatically pull a data table from our ERP
system (through an ODBC connection). The data table consists of part
detail information that serves as the basis for our forecasting/budget
process. The data table is quite large (approx. 2000 rows by 30
columns) and is not normalized.

The table column names do not change therefore I know what columns to
break-up in order to normalize the table(s). Therefore I would like to
automatically, everytime the data table is pulled from the ERP system,
normalize the table by breaking it up into a few tables. I would like
to do this automatically through a "make table query" (or similar
process), however I cannot find any information that tells me whether
this possible.

I'd suggest instead having a set of normalized tables in your
database; you could import, or better, link to your ERP ODBC
connection, and run a series of Append queries to migrate the data
from the wide-flat table into your normalized structure. These queries
can be run in sequence using either a macro or (better) VBA code. If
you want to discard the previously imported data each time, first run
a series of Delete * FROM Tablename queries to empty the existing
tables. Be sure to Compact your database frequently if you do this -
the space for the deleted records will not be freed or reused.

John W. Vinson[MVP]