Hi Martin,
From your examples, I can't think of a way to do this in a query.
I think this may be possible using 2 tables, 2 recordsets.
Read the data in from recordset1 which is based on your existing table, then
write it back out using recordset2 to append it to the new table.
However ... at 80 fields per record x how many records are in your table ...
you're going to end up with a whole crapload of records in your new table!
I can only wonder why you want to do this, but hey ... it your database!
:-)
Here is my code, (tested and working) but you may have to inspect the
numeric value of your "tblOldTable's" AutoNumber field's .Attribute.
************************************************** ***
Private Sub Command1_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst1 As DAO.Recordset
Set rst1 = MyDB.OpenRecordset("tblOldTable", dbOpenTable)
Dim rst2 As DAO.Recordset
Set rst2 = MyDB.OpenRecordset("tblNewTable", dbOpenDynaset)
Dim fld As Field
Dim strFld As String
Dim varData
Dim MyID As Long
With rst1
.MoveLast
.MoveFirst
Do Until .EOF
For Each fld In .Fields
strFld = fld.Name
varData = .Fields(strFld)
'Debug.Print strFld
'Debug.Print fld.Attributes
If fld.Attributes = 49 Then 'This is the Attribute of my
AutoNumber Field
MyID = varData
GoTo NextField
End If
With rst2
.AddNew
!RecordID = MyID
!FieldName = strFld
!FieldData = varData
.Update
End With
NextField:
Next fld
If Not .EOF Then
.MoveNext
End If
Loop
End With
Set rst2 = Nothing
Set rst1 = Nothing
Set MyDB = Nothing
End Sub
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
(Access 2000)
Two issues:
Within a query, I need to return a field name as data (see eg. below).
I need to search within 80 fields (same criteria) - is there a way to
avoid 80 separate expressions (the 80 field names are stored in a
table - can I get the query to look each of these up?)?
Here's an example:
CURRENT DATA SAMPLE:
(Field names:) Field 1 Field 2 Field 3
(Data:) 14
15
16a
RESULTS DESIRED:
(Field names:) Field Name Data
(Data:) Field 1 14
Field 2 15
Field 3 16a
Note that 'Field 1', formerly the name of the field, is now desired as
tabular data. This seems, from my very basic research, to be the
reverse of a crosstab query.
Any ideas are greatly appreciated - thanks!!!
Martin Lacoste
