how do i append a column in access from one table to another?

M

Mike

I have two tables that I am working with. Both of them have order_number so
that could be the relationship. I am trying to get the order_date from one
table to the main table only if maintable's order_number = secondary
order_number. The main table has the column for order_date with no data. The
seconday table has the order_date with the data I want. I need to append the
order_date data from the secondary table to the main table. Please Help.
 
K

KARL DEWEY

You are using the wrong term for what you want to do.
Append adds new records.
Update changes data in existing records.

Create a query and click on the menu VIEW - View SQL. In the window paste
the SQL statement below and edit with your table names.

UPDATE MainTable INNER JOIN SecondaryTable ON MainTable.order_number =
PJ.order_number SET MainTable.order_date = SecondaryTable.order_date;

Use your real table names instead of MainTable and SecondaryTable.
 
M

Mike

Thank you that worked perfectly! One more quick question. The date that is
being copied over is displayed in this format: 20070129. How do I change that
to 01/29/2007?
 
K

KARL DEWEY

Formats are set in the display such as query, form, or report.

First, are you sure it is a date datatype field or is it text?
 
M

Mike

It is a text field.

KARL DEWEY said:
Formats are set in the display such as query, form, or report.

First, are you sure it is a date datatype field or is it text?
Click to expand...
 
J

John Spencer

Perhaps you can use the following expression
DateValue(Format(YourTable.YourField,"@@@@/@@/@@"))

That will fail if YourTable.YourField cannot be interpreted as a date. So
if that could be the case, you might want to use

IIF(IsDate(Format(
"dd","@@@@/@@/@@")),DateValue(Format(YourTable.YourField,"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top