Syntax for update query using two table.

[Mario]

I have tbl1, as follows:

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75



I have tbl2, as follows:
StudentID Name English Math
1 Andy 80
2 John 85
3 George 75




I want to update the missing values of tbl2 using the
data from tbl1.
Which will result in the following state of tbl2 after
update

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75


Please help.

Thanks
Mario

 

[John Vinson]

I have tbl1, as follows:

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75



I have tbl2, as follows:
StudentID Name English Math
1 Andy 80
2 John 85
3 George 75




I want to update the missing values of tbl2 using the
data from tbl1.
Which will result in the following state of tbl2 after
update

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75

Create a Query joining tbl1 to tbl2 by StudentID; first, make sure
that StudentID is the Primary Key of both tables.

Change the query to an Update query, and update tb2.English to

NN([tb2].[English], [tb1].[English])

and similarly for the other (improperly normalized :-{( ) subject
fields.

What do you want to happen if tb1 has a score of 80 for Andy, and tb2
has a score of 85? The query as written will leave the existing value
unchanged unless it is NULL.

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

 

[Mario]

What does "NN" in "NN([tb2].[English], [tb1].[English])"
mean.

If you dont mind can you please let me know the complete
SQL.

Thanks for your help.

-----Original Message-----

I have tbl1, as follows:

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75



I have tbl2, as follows:
StudentID Name English Math
1 Andy 80
2 John 85
3 George 75




I want to update the missing values of tbl2 using the
data from tbl1.
Which will result in the following state of tbl2 after
update

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75

Create a Query joining tbl1 to tbl2 by StudentID; first, make sure
that StudentID is the Primary Key of both tables.

Change the query to an Update query, and update tb2.English to

NN([tb2].[English], [tb1].[English])

and similarly for the other (improperly normalized :- {( ) subject
fields.

What do you want to happen if tb1 has a score of 80 for Andy, and tb2
has a score of 85? The query as written will leave the existing value
unchanged unless it is NULL.

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

 

[Mario]

Hey,

I used my brains a little bit and got it.

Thanks & May god bless you.
Mario.

-----Original Message-----

I have tbl1, as follows:

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75



I have tbl2, as follows:
StudentID Name English Math
1 Andy 80
2 John 85
3 George 75




I want to update the missing values of tbl2 using the
data from tbl1.
Which will result in the following state of tbl2 after
update

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75

Create a Query joining tbl1 to tbl2 by StudentID; first, make sure
that StudentID is the Primary Key of both tables.

Change the query to an Update query, and update tb2.English to

NN([tb2].[English], [tb1].[English])

and similarly for the other (improperly normalized :- {( ) subject
fields.

What do you want to happen if tb1 has a score of 80 for Andy, and tb2
has a score of 85? The query as written will leave the existing value
unchanged unless it is NULL.

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

 

[Mario]

Hey,

I used my brains a little bit and got it.

Thanks & May god bless you.
Mario.

-----Original Message-----

I have tbl1, as follows:

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75



I have tbl2, as follows:
StudentID Name English Math
1 Andy 80
2 John 85
3 George 75




I want to update the missing values of tbl2 using the
data from tbl1.
Which will result in the following state of tbl2 after
update

StudentID Name English Math
1 Andy 80 90
2 John 95 85
3 George 75

Create a Query joining tbl1 to tbl2 by StudentID; first, make sure
that StudentID is the Primary Key of both tables.

Change the query to an Update query, and update tb2.English to

NN([tb2].[English], [tb1].[English])

and similarly for the other (improperly normalized :- {( ) subject
fields.

What do you want to happen if tb1 has a score of 80 for Andy, and tb2
has a score of 85? The query as written will leave the existing value
unchanged unless it is NULL.

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

 

[John Vinson]

What does "NN" in "NN([tb2].[English], [tb1].[English])"
mean.

It means my fingers fumbled attempting to type NZ (the Null To Zero
function). Sorry! :-{)

If you dont mind can you please let me know the complete
SQL.

UPDATE tbl2 INNER JOIN tbl1
ON tbl2.StudentID = tbl1.StudentID
SET tbl2.English = NZ([tbl2].[English], [tbl1].[English]),
tbl2.Math = NZ([tbl2].[Math], [tbl1].[Math]);

Thanks for your help.

You're welcome and sorry about the confusion!

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