Union Query

[dixxy]

HEllo,

I am having trrouble with a Union query.

Is the recordset produced by a Union query supposed to be updateable? In other words, if i have form that is based on a Union query, can I modify a record from that recordset throught the form?



Thanks,

Sylvain

 

[Golom]

UNION queries are not updatable because the system can't determine which of the SELECTs in the UNION produced the record and therefore doesn't know which table to change.

 

[dixxy]

Ok thanks....that is what i was afraid of.....

Is there any way around this?

Thanks,

Sylvain

 

[Golom]

Several ... but what's going to work for you depends on the details of the problem you are trying to solve.

You could give the user a button "Change this Record" and copy the field's to a text box (or boxes) that are unbound. Let them change values in the text boxes and then issue an SQL UPDATE statement to change the base tables (assuming that YOU can figure out which table to change!)

Alternatively, do a maketable from your UNION query and let them modify that table. Run an update from the table they are changing to the real tables on which the union was based. This takes a bit more organization to pull off.

The basic problem remains however. If you have a UNION query like this

Select f1, f2, f3 From tbl1
UNION
Select a1, a2, a3 From tbl2

then there is no way to tell if a record came from "tbl1" or from "tbl2" and you therefore can't reliably do the update of those tables when something changes. You would need something like.

Select 'tbl1' As [Source], f1, f2, f3 From tbl1
UNION
Select 'tbl2', a1, a2, a3 From tbl2

to allow that identification.

 

[cheerio]

BUT note that the second piece of code above may give different answers from the first.

A union query usually suppresses duplicates but the extra field in the second query guarantees that items that exist in both tables will be reported twice because the table source field is bound to differ.

The desire to update a union query suggests the database design could be wrong. If the data is so similar why not have it all in one table, perhaps with an additional column indicating the difference. For example, if your union joins a table of New York employees to a table of Washington employees you just have one combined table but with an additional column indicating employee location.

Generally I use UNIONS for reporting - for example to count employee leavers by year where long left employees sit in an archive table.