Update Record

[Guest]

I have 2 tables (an example has been attached at the bottom). I want to
update the date in Table 1 if it is blank and keep the date as table 1's
original data if it is not blank. I have made a new table (table 3) to show
the final result that I want. Could anyone help me to solve this? Thanks in
advance.

Table 1
Product No. Date
1 1/1/2004
2
3 3/1/2004
4
5

Table 2
Product No. Date
1 5/1/2004
2 5/1/2004
3 5/1/2004
4 5/1/2004

Table 3
Product No. Date
1 1/1/2004
2 5/1/2004
3 3/1/2004
4 5/1/2004
5

 

[Todd Shillam]

You just need to create an update query. By default, Microsoft Access
creates a 'select' query--just change it to an 'update' query (look at the
toolbar options to change). After you change your query to an update query,
locate your date field. In the date field's criteria block, enter the
following:

Is Null

Then just a couple blocks above (should read 'Change To') enter whatever
date you'd like to replace the null values with. Then click the 'run' icon
on the toolbar--Access will display a warning message stating how many
records you are about to change if all goes well.

Best regards,

Todd

 

[Guest]

Can I update the data (Table A) according to the data in another table(Table
B)? In addition, I want to keep the data in Table A even if there is a new
data in Table B. I only want to update those data which are blank in Table A
and with new data in Table B.

 

[John Vinson]

Can I update the data (Table A) according to the data in another table(Table
B)? In addition, I want to keep the data in Table A even if there is a new
data in Table B. I only want to update those data which are blank in Table A
and with new data in Table B.

You must - no option! - have a unique Join field linking the two
tables, so that you can specifically identify which record in TableA
should be updated with which record in TableB.

Let's say you have a field TableBID which is the Primary Key of
TableB, and a foreign key TableBID in TableA. You could create a query

UPDATE TableA
INNER JOIN TableB
ON TableA.TableBID = TableB.TableBID
SET TableA.ThisField = NZ(TableA.ThisField, TableB.ThisField),
TableA.ThatField = NZ(TableA.ThatField, TableB.ThatField)
....

If you do not have a joining field, there is NO way for Access to
determine which of the many records in TableB should be the source of
the data to update a record in TableA.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps