Manipulating and appending data from multiple source files

[johngwesterman]

I am trying to import multiple files that are in different layouts into a single Access database table. I have written a couple of append queries that take the imported data from several temporary tables, perform cleansing operations on a couple of fields, and append the cleansed records into a single table that I use for reporting. The problem is that I currently have to write a seperate SQL statement for each input file which is messy and will be impossible to maintain - I'd like to have one generic SQL statement that I can run each input file through, possibly using variable names for the SELECTED fields, but given the different data layouts, and different operations that need to be performed given a particular input field, I'm not sure how to do this.

Anyone have any ideas?

 

[MichaelRed]

Way much more than will be covered here (by me).

I "ASS U ME" that there is some what of a regular pattern to the overall process?

This would suggest that you know the pattern. It follows then that you can "Map" the errant fields (the ones needing V&V)? As in there are certain (currency?) fields which have limits on them, but they are (in their native instantation) nammed differently. For this, could you alias them on input to have a common naming convention? Then write a module which generates unnammed querydefs from the filed list of the temp sources (with the alias names), using hte namming convention to assign the fields to the V&V routines?

Hmmmmmmmmmmmmm, the above (probably?) sounds like the ravings of a maniacial fool. Sorry, I can't think of an easier way to state it without going into some examples. Unfortunatly, they would be hard to generate, not very relevant to your specific problems, and not 'present' well in the text only format of these forums.

Well, perhaps you - or someone else in the "Guru" category can at least understand the concept.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[KevCon]

Find duplicates/ merge / Excel trick:
The quickest method, what I do, is copy and paste everything into Excel. This way I can move and manipulate everything into a big list, especially if there are varying fields and datatypes among various tables that you wish to merge into one result.
Then, sort by the unique field or key.
Now, flag the duplicates:
Insert a column next to the field, and autofill all the way down a formula like =if(A2=A1,1,0) Then turn on AutoFilter, filter for "1" in that column, and delete the filtered result of duplicates.

 

[MichaelRed]

KevCon,

Yes, ... but .... this is more - or - less what johngwesterman appears to be attempting to avoid. All of that messy manual manure mucking - to get to the nice clean data.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over