They have an excel spreadsheet with 200 variables and 200
descriptions

I can import this into access to have it for work.

I need to make a new table with the variables and descriptions
from this table

how would I do that?
the variables are already there but I don't know how to write the
descriptions to the description of the variables.

I asked and got a new excel spreadsheet with more into....NOT MUCH
HELP LOL



this time they gave me variable name, description and format(text or
int)


can someone tell he how I would write this to a new table?

If using Access 2003 or older, do it like this:

Using the Query Builder, create a new Query, including all Fields from the
existing Table, on the menu, under Query, choose Make Table, and when
prompted enter the name you want for the new Table, then execute this Query
by clicking the exclamation point button in the toolbar.

If you are using Access 2007 or newer, the Make Table Query is still
available, but user interface is different so you'll either have to look it
up in Help or find the equivalent functions in The Ribbon interface --
perhaps someone else can give you details.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"sparks" <sparks (AT) home (DOT) com> wrote

yes but it still does not allow me to write the descriptions to the
variables.

I think I am going to have to do an update or something to be able to
write the description to the variable description in the table


On Wed, 31 Aug 2011 11:02:53 -0500, "Access Developer"
<accdevel (AT) gmail (DOT) com> wrote:

Table1 with 200 variables
Table2 with 2 variables variablename and description each one has 200
names and 200 descriptions


I have to match the variables in the table 1 with the variablename in
table 2

But I am not sure about having 2 tables open read from rst1 and write
to rst2 ???
set rst1 as db.openrecordset("codebook")
set rst2 as db.openrecordset("newtest")

Ok how do I loop thru rst1 and compare to rst2

If Rst1.variable = rst2.variablename
Then
Fld.name = rst2.variablename
strDescrip = rst2.description

Then do this to rst1
SetPropertyDAO(fld, "Description",dbText, strDescrip)




table1

bob text blank
fred text blank

table2
variable description
bob this is bob
fred this is fred



not sure if this will support ascii spaces and tabs

What do you mean by "variables"? Variables contain information in VBA code,
not in tables.

Tables are defined with Fields (aka Columns) with Records (aka Rows) that
contain data.

It's not clear to me from your description just what you have and what you
are trying to accomplish. It's confusing when not using the accepted
terminology.

A Table with 200 Columns (Fields) would almost certainly not have been
implemented according to relational database principles, and thus not
normalized, and would be very difficult to work with. A table with 200 Rows
(Records) of data, however, would be reasonable... and, in fact, a table can
have many, many more than a few hundred Rows.

Are you trying to update data in one table (Table 1) with data (Description)
from matching records in another table (Table 2)? Or are you trying to
define a table (Table 1), by obtaining the names of its Columns (Fields)
from data stored in another (Table 2)?

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"sparks" <sparks (AT) home (DOT) com> wrote

from what I can tell they had a code book and put it in access.
it contains variable name and description
someone made a table with the variable name field creating a new table
with the 200 variables in that field
they did not take the 200 descriptions and put them with the variables

variable name description
bob bob description
fred fred description


the new table has
bob
fred

as variables and text but no description

I can read thru the descriptions in the codebook table
but I can not write the description to the new table.

bob text
bob text bob description

this is what I need to do but I don't know how.

I thought about passing the variable name and description to a
function

dlookup in new table for variable name
if it matched I would create the description of the variable in new
table.

This is new stuff to me and It is very hard for me to work with 2
tables at one time.

I decided to just try creating the description in the new table on
each field.

I can read thru the codebook fine and get the fieldname and
description
pass it to my function but I can not create the description
I dont seem to have the skill to do this



Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim rst As Recordset
Dim fld As Field
Dim obj As Object
Set db = CurrentDb
Set rs = db.OpenRecordset("codebook")
If rs.RecordCount > 0 Then
With rs
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs!FieldName
Debug.Print rs!Description
Call writedesc(rs!FieldName, "description", dbText, rs!Description)
rs.MoveNext
Loop
rs.Close
End With
End If
Set rs = Nothing
Set rst = Nothing
End Sub

public function writedesc(fullname as string, strpropname as string,
valtype as integer, desctext as string)

Dim rst As Recordset
Dim fld As Field
'Dim obj As Object
dim finder as variant

Set db = CurrentDb
Set rst = db.OpenRecordset("newtest")

finder = DLookup(rst!fullname, "newtest", "rst!fullname = fullname")
if finder
rst!fullname = fullname.CreateProperty("description", dbText,
"fullname")
End If
SetPropertyDAO = True

End Function

I understand what you are saying to be:

You have one table which has two fields, named Variable_Name and
Description, populated with data. You have another table with only one
field, named Variable_Name, populated with the same data. You wish to add a
Description field to the second table, and populate it with the description
data corresponding to the Variable_Name in the second table.

I assume the following which you did not say:

Variable_Names are unique (not duplicated in the same table). This
operation is needed once, not on a periodic schedule.

Advice (may not be critical in this case): Do not use Access reserved words
(e.g., Description) as names of database objects. At best, this can be
confusing when you look at your database. At worst, it can interfere with it
working as you expect. (In the rest of this post, I'm using
Variable_Description as the name of that field.

Here are some simple solutions:

(1) If you have no additional fields populated with data in the second
table, the simplest way is to recreate it. Copy the first table, with data,
then open it in design view, and delete all the unneeded
fields.

(2) If you do have additional fields in the second table already populated
with data, open that table in design view, and add the Variable_Description
field. Create a new select Query containing both tables, joined on the
field, Variable_Name, in each table, and including the Varible_Description
field from the second table -- run it to make sure it returns the proper
information. Now, with that Query in design view, on the menu (Access 2003
or earlier) click on Query, and choose Update. In the Update To line, below
the Variable_Description field for the second table, enter the table.field
form of reference to the Variable_Description Field of the first table.

Following is the SQL that worked for me to confirm my suggestion # 2:

UPDATE FirstTable INNER JOIN SecondTable ON FirstTable.Variable_Name =
SecondTable.Variable_Name SET SecondTable.Variable_Description =
[FirstTable].[Variable_Description];

In neither case do you need to create the field in code, nor write code to
populate it (though both are possible). Access' strong point is that you
can do a great deal of work with minimal effort, and that is the case here,
if I understand your description. BTW, just a "sample" is often not enough
description... you know, and you have the database in front of you, but we
have to rely on your description, so it expedites a useful answer to review
what you wrote to assure that it is clear, concise, but sufficiently
detailed that one of us who does not already know about the database, and
doesn't have it in front of us, can understand.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access