Edit table using union query

  • Thread starter Thread starter De'Ville
  • Start date Start date
D

De'Ville

I am trying to create a form for a union query that
allows me to edit the data in either of the tables used
for the query, but I can't understand why with some
queries I can edit the table and others I cant

Can anyone tell me why when I open some queries I can
edit the data in table behind the query where others
don't allow me to?

Eg here are two union queries which won't allow me to
edit the data in the table tblEnquiries for example
1.
SELECT *
FROM [tblEnquiries]
UNION SELECT *
FROM [tblIssues];

2.
SELECT [Status], [Subject]
FROM [tblEnquiries]
UNION SELECT [Status], [Subject]
FROM [tblIssues]
ORDER BY [Subject];

Where as here is an example of a query which will allows
me to edit the data in the table

SELECT tblEnquiries.Date, tblEnquiries.Status,
tblEnquiries.MainCat, tblEnquiries.SubCat,
tblEnquiries.Highlighter, tblEnquiries.EscalatedTo,
tblCustomers.CustomerType
FROM tblCustomers INNER JOIN tblEnquiries ON
tblCustomers.CustomerID = tblEnquiries.CustomerID;

Thanks in advance if you can explain why
 
Hi,



A UNION query adds a DISTINCT, implicitly, silently, for you. A GROUP BY
query, or a query with DISTINCT, is not updateable.

A UNION ALL query could be updateable, in theory, but the design decides
to not implement that exception, so, any UNION query is not updatable.

The last query as no UNION, no GROUP BY (distinct, aggregate,
transform), and the equi-join allows to precisely know in which table, and
which record in that table, has to be updated. It is not the case if the
result is a result of a UNION, or from a GROUP BY.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top