Is there an easy way to convert a db's fields into records??? 1

[lewie]

I have a db where alot of entries were placed into fields.
They should be records. is there an easy way to transfer them other than transcribing them.
TIA
Lewie

 

[jimb0ne]

I'm not exaclty sure what you mean by they were entered as fields. Records are made up of fields, in a table, please clarify what you are speaking of...

 

[lewie]

you know how you would make a table
typeID typename
well this is a table with fields
atype text
btype text
ctype text
dtype text
ect for all the types.
no records.

 

[jimb0ne]

A record is basically a collection of all the fields from a specific row in your table. So with your above example a "record" would be aType and text, or bType and text, the record would contain both typeID and typeName is what I'm trying to say. When you set the recordsource of a form to a table(like the one above), the "recordset" of the form would contain the information from your table, record 1 would have aType and text, record 2 would have bType and text, etc. Please let me know if this helps clarify your question.

 

[luceze]

Could you make a crosstab query with the Type ID as the column heading then copy and paste into a new table?

Eric

 

[lewie]

What I have is a table colors.
the fields are
red green blue orange velvet grey brown black
there are no records.
I want to convert the table colors to
fields
color id color
and the records to red green blue ect.
now do you get what i mean.

 

[jimb0ne]

so you have a table, which has been created with field names that you now want to be actual field values of the "Color" field? If this is the case I could provide some code to populate the "Color" field with the names of the old fields.

 

[lewie]

exactly

 

[jimb0ne]

you can do this programatically by using code like the following.

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim db As DAO.Database
Dim iCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Tester;&quot
Set rs2 = db.OpenRecordset("SELECT * FROM Forecast;&quot

If rs2.RecordCount = 0 Then
rs2.AddNew
rs2!Color = "blah"
rs2.Update
rs2.Bookmark = rs2.LastModified
End If
rs2.MoveFirst
For iCounter = 0 To rs.Fields.Count - 1
If rs2.AbsolutePosition = -1 Or rs2.RecordCount = 0 Then
rs2.AddNew
rs2!Color = rs.Fields(iCounter).Name
rs2.Update
rs2.Bookmark = rs2.LastModified
rs2.MoveNext
Else
rs2.Edit
rs2!Color = rs.Fields(iCounter).Name
rs2.Update
rs2.Bookmark = rs2.LastModified
rs2.MoveNext
End If
Next iCounter

basically, just place the code into the click event of any button, change the 2 table names from "Tester" and "Forecast" and also make sure that the field name in the new table is named "Color", or change the !Color references in the above code to the correct field name. Let me know how this works. Once you press the button once the values will be moved to the new table.

 

[lewie]

Worked good.
Thanks alot.