Union ALL problem

[witooldas]

Hi there,

I have a question about Union ALL function.

In then Database I have two tables.


In first table "Data" I have a shops with address and etc. At the second table "Sub Data" I have names of premises. Tables are connected by Data.ID and [Sub Data].data_ID.

Now, I want to show all record in one query where Data.ID and [Sub Data].ID will be in one column. I did that with “Union ALL” function, but the problem is that I can't now edit records.

The problem is that I have split form which displays all orders, but to know who is writing I must to union tables with all existing objects and then search in it to display in form address and etc..

Example:

“Data” table:
|ID | Address | Type | etc | Engineer_ID |
------------------------------------------------------------------------------
|X010 | Vilnius, Vilnius str. 7 | XXX | ... | 2 |
|X020 | Vilnius, Kaunas str. 8 | XX | ... | 1 |
|X030 | Vilnius, Gediminas str. 9 |Warehouse| ... | 2 |

“Sub Data” table:
| ID | Type |Data_ID|
-------------------------
|X011|Bakery | X010 |
|X021|Cookery| X020 |

So with Union ALL funcion I union those 2 tables:
SELECT
Data.X,
Data.Address,
Data.Type,
...
...
Data.Engineer_ID
FROM Data

Union ALL
SELECT

[Sub Data].X,
Data.Address,
[Sub Data].Type, Data.Energ_Imone,
...
...
Data.Engineer_ID
FROM Data RIGHT JOIN [Sub Data] ON Data.ID = [Sub Data].[Data_ID];


|ID | Address | Type | etc | Engineer_ID |
------------------------------------------------------------------------------
|X010 | Vilnius, Vilnius str. 7 | XXX | ... | 2 |
|X020 | Vilnius, Kaunas str. 8 | XX | ... | 1 |
|X030 | Vilnius, Gediminas str. 9 |Warehouse| ... | 2 |
|X011 | Vilnius, Vilnius str. 7 |Bakery | ... | 2 |
|X021 | Vilnius, Kaunas str. 8 |Cookery | ... | 1 |

Maybe someone knows how can I union two tables, but still will be able to edit records.

Thanks a lot

[orange]

I think you will get better and meaningful advice if you describe exactly what you are trying to do. The Union ALL is a HOW you might do whatever it is you are trying to do, Focus on What, not How, and I'm sure others will offer advice once they understand your situation.
Perhaps you could post a jpg of your Tables and relationships.