CONVERTING COLUMNS TO ROWS IN ACCESS 2000

[SEAN DI''''ANNO]

The day, I was alwasy dreading in my professional career has finally
happened. After keeping a low profile for many years I have been asked to
write an access database for my MD.

I have to try to work out the average day's qty required for materials (Item
Numbers). I won't pretend, i only just found out what MRP meant let alone
know the concepts. Anyway, Im not very good at coding but I think the first
part of the problem is quite straight forward if some one could show me some
sample VB code.

A table is imported from SAP into an access table along the lines of;

Item No Jan Feb Mar Apr
12345 1 2 3 4
23456 1000 1 1 1

The first problem is that the file coming in is a flat file of columns and I
will never really know how many months are being exported. Therefore, what
code can I use to convert the code into this format


Item No Month Qty
12345 Jan 1
12345 Feb 2
12345 Mar 3
12345 Apr 4

The second part of the problem is probably more advice and less anybody out
there has had to deal with this before, If a product is used regularly every
month, I guess it will be quite easy to work out an avg days requirement
based on usuage and number of working days in the month etc...but if a
product has suddent fluctutations in usgae what is the best way of dealing
with working out an average. if anybody could offer advice, I would really
appreciate it Please!!!

 

[Douglas J. Steele]

In essence, you need to create a UNION query along the lines:

SELECT ItemNo, "Jan" As WhatMonth, Jan As Qty
FROM MyTable
UNION
SELECT ItemNo, "Feb" As WhatMonth, Feb As Qty
FROM MyTable
UNION
SELECT ItemNo, "Mar" As WhatMonth, Mar As Qty
FROM MyTable

and so on. (Note that Month is a reserved word, and so shouldn't be used as
a field name)

You should be able to generate that query dynamically, based on the fields
in the table.

For example, something like the following untested air-code:

Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String

' Loop through all of the fields in the table.
' Create a subquery for each field not named ItemNo.

Set tdfCurr = CurrentDb.TableDefs("MyTable")
For Each fldCurr In tdfCurr.Fields
If fldCurr.Name <> "ItemNo" Then
strSQL = strSQL & "SELECT ItemNo, """ & fldCurr.Name & _
""" As WhatMonth, " & fldCurr.Name & " As Qty " & _
"FROM MyTable UNION "
End If
Next fldCurr

' Remove the extra " UNION " from the end.

If Len(strSQL) > 0 Then
strSQL = Left$(strSQL, Len(strSQL) - 7)
End If
Next fldCurr