Help with Loop Apend Query in VBA

[Chase]

I have a table of 14 colums, first 2 are text and the last 12 are numeric
with the field name eg. Jan-08, Feb-08, etc

I need to take this data month by month and append it to a table of 4 colums
with the first 2 colums as text then a date field and then a value field.

What is the VBA code for DoCmd.RunSQL and the loop code I need to use?

Regards

Chase

 

[Klatuu]

You don't have a database, you have a spreadsheet. You have back yourself
into a really ugly corner. Having field names like Jan-08, etc means every
year you have to change your table field names and all code that reference
these fields. You would be way better off to radically change your design so
you have the two text fields, a field name Period, and a value field, much
like the second table you are describing.

The it would be much easier to create a query that would pull the data from
the 4 column table into the "spreadsheet view" your table is now in.

 

[Chase]

Klatuu

I am importing a 30,000 line spreadsheet hence the 14 fields and the field
names. I need to convert this to the 4 fields as stated so that I can use the
2 text fields which have relationships to other tables to enable me to
generate exports. I tried attaching all the relevant data in excel with
vlookups, but the PC is not powerful enough to deal with the volumes of data
and the pivots required.

Please can you help with VBA code that will process the Append query so that
can reference a text box on a form so I don't have to open the query change
the Jan-08 to Feb-08 and do that for each of the other 10 months.

Regards

Chase

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NormalizeDenormalize.mdb" does something very similar.
Perhaps if you downloaded it and worked through it, it would give you some
ideas.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L