Really a Query question

[JohnB]

Hi. Sorry about this but I need to ask this question and I
cant get access to the Queries section.

I can provide more info if needed but say I have two
tables, one with SchoolID as the Key Index and the other
with SchoolID as a number field. Each table also has a
field called Status. How do I set up a query that copies
the Status field value from the second tables records to
the corresponding first tables records. e.g. the second
tables Status value for the record whose SchoolID is 233
gets copied into the first tables Status field in the
record where SchoolID is also 233.

I know you will say, why not just link the tables by
SchoolID but this is a one off thing and once done I will
use the first table and dispose of the second.

Ive used Update queries before but only to change a field
value, in a set of records, using one I specify myself.
This is a sort of Update Query with a Where clause.

Can anyone help please? Again, sorry for using the wrong
section. Thanks, JohnB

 

[Guest]

Hi, John.

You've almost answered your own question. In a new update query, show both
tables, and establish a link on SchoolID.

Drag the field to be updated to the grid, and specify the "Update To:" value
by a full referral to the Status field of the second table:

[School2].[StatusID]

The SQL will be something like:

UPDATE School1 INNER JOIN School2 ON School1.SchoolID = School2.SchoolID SET
School1.Status = [School2].[Status];

Hope that helps.
Sprinks

:

 

[JohnB]

Hi Sprinks. I think you meant [School2].[Status]
but never mind. Ive now done as you suggested and it
works perfectly. Thank you very much for your help. JohnB

-----Original Message-----
Hi, John.

You've almost answered your own question. In a new update query, show both
tables, and establish a link on SchoolID.

Drag the field to be updated to the grid, and specify the "Update To:" value
by a full referral to the Status field of the second table:

[School2].[StatusID]

The SQL will be something like:

UPDATE School1 INNER JOIN School2 ON School1.SchoolID = School2.SchoolID SET
School1.Status = [School2].[Status];

Hope that helps.
Sprinks

:

Hi. Sorry about this but I need to ask this question and I
cant get access to the Queries section.

I can provide more info if needed but say I have two
tables, one with SchoolID as the Key Index and the other
with SchoolID as a number field. Each table also has a
field called Status. How do I set up a query that copies
the Status field value from the second tables records to
the corresponding first tables records. e.g. the second
tables Status value for the record whose SchoolID is 233
gets copied into the first tables Status field in the
record where SchoolID is also 233.

I know you will say, why not just link the tables by
SchoolID but this is a one off thing and once done I will
use the first table and dispose of the second.

Ive used Update queries before but only to change a field
value, in a set of records, using one I specify myself.
This is a sort of Update Query with a Where clause.

Can anyone help please? Again, sorry for using the wrong
section. Thanks, JohnB

.