Update qry w/ a union qry?

[Mercy]

Thanks Jake for helping and replying

I figured out the problem: One of the tables
(namely "Details" ) is actually a UNION qry... and for
some reason that causes Access to not allow the Update
qry to be run.

Details is a (Table Cloth & Something) Union
(Table "Stuff" & Something) ...

I don't see why it should be a problem though? When
I "preview" the records that are going to be changed,
they are all there. But I just can't update them. Can
anyone tell me what's going on?

Access gives me an error message that says: "This must be
an update qry." Or something like that... my chinese is
crummy... and I'm using Chinese Office (-_-)

This is the Update qry I've been trying to use ... which
works fine when you have two TABLES ... but not when one
of them is a Union Qry...

UPDATE Cloth INNER JOIN Details ON
(Cloth.ProductID = Details.ProductId)
AND (Cloth.PONum = Details.PONum)
SET Cloth.Marked = "Yes"
WHERE Details.OrderNum)=[X];

If anyone can help me, I'd really appreciate it!

-Mercy

 

[Duane Hookom]

Union queries are not updateable. You would need to update the tables
separately.

 

[Mercy]

Thanks Duane,

Thanks for telling me that Union qry's aren't update-
able... I never knew that.

But I'm not trying to update the Union Query .... I'm
trying to update something based on it's relationship to
the Union Query... Doesn't that make it a different case?

-----Original Message-----
Union queries are not updateable. You would need to update the tables
separately.

--
Duane Hookom
MS Access MVP

Thanks Jake for helping and replying

I figured out the problem: One of the tables
(namely "Details" ) is actually a UNION qry... and for
some reason that causes Access to not allow the Update
qry to be run.

Details is a (Table Cloth & Something) Union
(Table "Stuff" & Something) ...

I don't see why it should be a problem though? When
I "preview" the records that are going to be changed,
they are all there. But I just can't update them. Can
anyone tell me what's going on?

Access gives me an error message that says: "This must be
an update qry." Or something like that... my chinese is
crummy... and I'm using Chinese Office (-_-)

This is the Update qry I've been trying to use ... which
works fine when you have two TABLES ... but not when one
of them is a Union Qry...

UPDATE Cloth INNER JOIN Details ON
(Cloth.ProductID = Details.ProductId)
AND (Cloth.PONum = Details.PONum)
SET Cloth.Marked = "Yes"
WHERE Details.OrderNum)=[X];

If anyone can help me, I'd really appreciate it!

-Mercy

.

 

[John Spencer (MVP)]

No, it doesn't. If a query uses a second query that in not updateable then the
first query is not updateable. You will have to find a way around this limitation.

Without knowing a lot more about the structure of your tables it will be hard
for anyone to give you specific advice.

You create a table using the Union query as a source and then use that in your
update, or you can create a VBA solution, or you can restructure your tables to
handle the situation or you might be able to restructure your queries or ...

Thanks Duane,

Thanks for telling me that Union qry's aren't update-
able... I never knew that.

But I'm not trying to update the Union Query .... I'm
trying to update something based on it's relationship to
the Union Query... Doesn't that make it a different case?

-----Original Message-----
Union queries are not updateable. You would need to update the tables
separately.

--
Duane Hookom
MS Access MVP

Thanks Jake for helping and replying

I figured out the problem: One of the tables
(namely "Details" ) is actually a UNION qry... and for
some reason that causes Access to not allow the Update
qry to be run.

Details is a (Table Cloth & Something) Union
(Table "Stuff" & Something) ...

I don't see why it should be a problem though? When
I "preview" the records that are going to be changed,
they are all there. But I just can't update them. Can
anyone tell me what's going on?

Access gives me an error message that says: "This must be
an update qry." Or something like that... my chinese is
crummy... and I'm using Chinese Office (-_-)

This is the Update qry I've been trying to use ... which
works fine when you have two TABLES ... but not when one
of them is a Union Qry...

UPDATE Cloth INNER JOIN Details ON
(Cloth.ProductID = Details.ProductId)
AND (Cloth.PONum = Details.PONum)
SET Cloth.Marked = "Yes"
WHERE Details.OrderNum)=[X];

If anyone can help me, I'd really appreciate it!

-Mercy

.