Update query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Crystal - 19 Jul 2006 18:29 GMT

I have 1 table named Anomaly and 1 table named QC. The anomaly table has the
fields X, Y, Target ID. The QC table has the fields X,Y, Target ID. The X,
Y fields are blank in the QC table. I want to make a query that will fill in
the X, Y fields in the QC table if the Target ID matches. How would I do
this?

Reply to this Message

Ken Sheridan - 19 Jul 2006 18:48 GMT

The answer to your question is:

UPDATE QC INNER JOIN Anomaly
ON QC.[Target ID] = Anomaly.[Target ID]
SET QC.X = Anomaly.X, QC.Y = Anomaly.Y;

But the real question is why are you duplicating data like this?

Ken Sheridan
Stafford, England

> I have 1 table named Anomaly and 1 table named QC. The anomaly table has the
> fields X, Y, Target ID. The QC table has the fields X,Y, Target ID. The X,
> Y fields are blank in the QC table. I want to make a query that will fill in
> the X, Y fields in the QC table if the Target ID matches. How would I do
> this?

Reply to this Message

Crystal - 19 Jul 2006 18:55 GMT

Do I type that in the update to field in the query?

> The answer to your question is:
>
[quoted text clipped - 12 lines]
> > the X, Y fields in the QC table if the Target ID matches. How would I do
> > this?

Reply to this Message

Ken Sheridan - 20 Jul 2006 13:10 GMT

You have two options. You can simply switch from query design view to SQL
view and type the whole SQL statement in, or you can create a normal query in
design view, joining the two tables on the Target ID fields. Add just the X
and Y fields from the QC table to the design grid and then change it to an
Update query. In the 'Update to' row for each field enter Anomaly.X, or
Anomaly.Y as appropriate.

Ken Sheridan
Stafford, England

> Do I type that in the update to field in the query?
>
[quoted text clipped - 14 lines]
> > > the X, Y fields in the QC table if the Target ID matches. How would I do
> > > this?

Reply to this Message