What queried should I use???

On Wed, 21 Feb 2007 21:22:00 -0800, Lee
wrote:

It's an access database.

Eeesh.

With just the one table, with account numbers as fieldnames, and no
way to control or know the fieldnames ahead of time!? Somebody goofed,
bigtime.

Could you post a *real* example of one of these tables, in the format

Tablename
Fieldname datatype
Fieldname datatype
Fieldname datatype

and give some indication of the range of possible values for the
column headers (I can't even bring myself to call them fieldnames,
since they are just SO spreadsheet)?

John W. Vinson [MVP]

I know. Sure. Please see below:

Tablename = Account
FieldName = Account number range from 100000 thru 999999 (in currency).

Let me know if you need anything else. Thanks for your big help again!

"John W. Vinson" wrote:

On Wed, 21 Feb 2007 21:22:00 -0800, Lee
wrote:

It's an access database.

Eeesh.

With just the one table, with account numbers as fieldnames, and no
way to control or know the fieldnames ahead of time!? Somebody goofed,
bigtime.

Could you post a *real* example of one of these tables, in the format

Tablename
Fieldname datatype
Fieldname datatype
Fieldname datatype

and give some indication of the range of possible values for the
column headers (I can't even bring myself to call them fieldnames,
since they are just SO spreadsheet)?

John W. Vinson [MVP]

On Fri, 23 Feb 2007 11:06:34 -0800, Lee
wrote:

I know. Sure. Please see below:

Tablename = Account
FieldName = Account number range from 100000 thru 999999 (in currency).

Let me know if you need anything else. Thanks for your big help again!

I'm sorry, Lee, that leaves me completely in the dark.

I know full well that you do not have a table with 899999 fields,
because Access couldn't possibly handle that.

That's all I know.

When you get a new set of data... *what do you get*?

A new .mdb file?

What's in it?

How is it being created - and why is it being created in this
hideously denormalized manner?

Could whoever is sending it be persuaded to use Access as if it were a
database, instead of a glorified spreadsheet???

John W. Vinson [MVP]

On Fri, 23 Feb 2007 11:06:34 -0800, Lee
wrote:

I know. Sure. Please see below:

Tablename = Account
FieldName = Account number range from 100000 thru 999999 (in currency).

Let me know if you need anything else. Thanks for your big help again!

ok... thought about it for a while and came up with a possible VBA
solution.

Assuming that you have a table [Accounts] with fields [Vendor] and an
arbitrary number of fields, where each field is an account number; and
a target table NewAccounts with fields Vendor, Account and Amount, try
this:

Private Sub MigrateAccount()
Dim db As DAO.Database
Dim tdf As DAO.Tabledef
Dim fld As Field
Dim qd As DAO.Querydef
Dim strSQL As String
Dim strFld As String

On Error GoTo Proc_Err

Set db = CurrentDb
Set tdf = db.TableDefs("Accounts")

For Each fld In tdf.Fields
strFld = fld.Name
If strFld "Vendor" Then
strSQL = "INSERT INTO NewAccounts([Vendor], [Account], [Amount])" _
& " SELECT [Vendor], " & Chr(34) & strFld & Chr(34) _
& "[" & strFld & "] FROM Accounts WHERE [" & strFld & "]" _
& " IS NOT NULL;"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
End If
Next fld

Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Number & " in MigrateAccount" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

This will loop through all the fields in Accounts, and generate a SQL
string
INSERT INTO NewAccounts([Vendor], [Account], [Amount]) SELECT
[Vendor], "003125", [003125] FROM Accounts WHERE [003125] IS NOT NULL;

(assuming that's one of the fields). It will then execute that query
and loop on to the next field.

John W. Vinson [MVP]