Enter a calculation for an empty column in a table

[Guest]

Hello,

I have had no luck entering a calculation to fill a newly added column in a table in design query view, ultimately I am going to need to do this in code. The below code is used to add an empty column by altering table zmax2. Then I execute a make table command. Finally, I need to figure out how to fill the new column based on columns from the two joined tables, zmax2 and rooting2. Can someone tell me how to give the calculation command to make the new column the Minimum of two columns, one from zmax2, the other from rooting2?

Dim strSQL As String
strSQL = "ALTER TABLE zmax2 ADD COLUMN MinMaxzvsr SHORT"

'Turn off warning message.
'DoCmd.SetWarnings False

'DoCmd.RunSQL strSQL

'Re-enable warning message
'DoCmd.SetWarnings True

strSQL = "SELECT zmax2.*, rooting2.* " & _
"INTO temp " & _
"FROM zmax2 LEFT OUTER JOIN rooting2 " & _
"ON zmax2.musym = rooting2.musym"

'Turn off warning message.
DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

'Re-enable warning message
DoCmd.SetWarnings True

RichardA

 

[John Vinson]

I have had no luck entering a calculation to fill a newly added column in a table in design query view

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

 

[Guest]

John,
Thanks for responding. Yes, I understand your point. However, my purpose is ultimately to export this table with the calculated column for a subsequent calculation using other software. Therefore, I need to do the calculation for the column. Would you mind telling me how to do it?

RichardA

 

[Rick Brandt]

John,
Thanks for responding. Yes, I understand your point. However, my

purpose is ultimately to export this table with the calculated column for a
subsequent calculation using other software. Therefore, I need to do the
calculation for the column. Would you mind telling me how to do it?

Queries can be exported as easily as tables so there is _still_ no need to
put this in the table.

 

[Guest]

I still do not know how to actually write the command. I try to use a MIN function but it only accepts one column argument. I need to know how to use the MIN or other function to assign to a column the minimum of two other columns.

RichardA

 

[Rick Brandt]

I still do not know how to actually write the command. I try to use a

MIN function but it only accepts one column argument. I need to know how
to use the MIN or other function to assign to a column the minimum of two
other columns.

RichardA

MIN() is for aggregating rows, not columns.

=IIf([Field1]<[Field2],[Field1],[Field2])

 

[John Vinson]

I still do not know how to actually write the command. I try to use a MIN function but it only accepts one column argument. I need to know how to use the MIN or other function to assign to a column the minimum of two other columns.

What we're trying to tell you is:

a. There is NO WAY to do this in a table. It is impossible. A table is
not a spreadsheet and cannot contain expressions.

b. There is NO NEED to do this in a table, since you can accomplish
your stated task - exporting the data - from a Query.

If you want to do it wrong regardless you will need to do it in two
steps. First create the table; then run an Update query to update the
field. The MIN function works across records, not across fields; to
return the lesser of two fields, use an expression

Least: IIF([Field1] < [Field2], [Field1], [Field2])