Editing Union results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a form driven by a Union query where the users can edit
the results.
SELECT KitName_1 AS KIT, KitAbbrv_1 as Abbrv,KitNotes_1 as KitNotes, Name,
SchName, District, Grade, ShipDate_1 AS ShipDate, ReturnDate_1 AS ReturnDate
FROM qryReturnSchedule
WHERE KitName_1 <> ""
UNION
SELECT KitName_2 AS KIT, KitAbbrv_2 as Abbrv, KitNotes_2 as KitNotes,
Name, SchName, District, Grade, ShipDate_2 AS ShipDate, ReturnDate_2 AS
ReturnDate
FROM qryReturnSchedule
WHERE KitName_2 <> ""
UNION SELECT KitName_3 AS KIT, KitAbbrv_3 as Abbrv, KitNotes_3 as
KitNotes, Name, SchName, District, Grade, ShipDate_3 AS ShipDate,
ReturnDate_3 AS ReturnDate
FROM qryReturnSchedule
WHERE KitName_3 <> ""
ORDER BY District, KIT;

Is there a way to do this?
 
Hi,


An UNION ALL query, neither an UNION query, is NOT updateable. Make a
temporary table out of it, and edit/use the temp table. You may have to
update the basic tables supplying that data, MANUALLY.



Hoping it may help
Vanderghast, Access MVP
 
Hi,

... well, instead of "manually", it would be more explicit to say
"EXPLICITLY". :-)



V.
 
I am trying to create a form driven by a Union query where the users can edit
the results.
Click to expand...

You can't. UNION queries are never updateable and cannot be made
updateable.

I'm a bit concerned about your table design. It appears that this
query is a "Normalizing Union Query" intended to take a non-normalized
table into a proper tall-thin table. Why not just base a MakeTable or
Append query on this UNION query, move all the table data into a
properly normalized table, base your form on that table, and delete
the spreadsheet-format table?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
You can't. UNION queries are never updateable and cannot be made
updateable.
Click to expand...

Actually, they can be made editable in some backend RDBMS, like Oracle
8i+, SQL Server 7.0+, etc., by using "INSTEAD OF" triggers on views made
up of "uneditable" queries like union queries.

The INSTEAD OF trigger "catches" the insert, update and/or delete events
and handles things.

It's either that, or write some sort of code (Stored Proc, VBA function)
that does this for you, but this is MUCH harder to pull off for Access,
because if a form's underlying dataset is read-only, your form's bound
controls will be read-only. So you end up having to write event handlers
to scrape-and-paste into unbound form controls, and then somehow turning
the data from the form back into another function back into the database.
Icky stuff worthy of figuring out a better way to do it in your database
design.

Chances are, your design is a common database design mistake. Instead of
having different tables with common information, you have one table with
the common information, and separate tables with the non-common
information. Something like this:

tblPerson:
personID
personTypeCode
lastName
firstName

tblEmployee
personID
employeeID

etc...

And, if you don't code the "person" code in a VBA Class, then you end up
having to do icky repetitive code because Access tables don't expose
anything approaching the functionality of triggers, but if your back-end
database is Oracle, SQL Server, MSDE, etc., then write triggers on the
table to create "child" records for you, which in this case might be like
(almost T-SQL):

set @newPersonID = select PersonID from inserted

if Inserted.personTypeCode = 'EM'
insert into Employee (personid) values(@newPersonID)

....and so on.




Probably the best you could do in the read-only form case is to have it
pop up various editable forms on the chunks of data these can all update.
Of course,
 
Back
Top