CODEWITHSUNDEEP
sqlvbams-access
Mark W
Mark W

Reputation: 1

Dynamic Union Query with Primary Key

I have a union query that is a dynamic reference to a group of tables covering varying types of items. I want to create a linked table (via query)that will have manual entry that will use the union query as a contributing reference list. Now of course without a primary key, the recordset will not be updateable. I would like the union query to remain a dynamic list of items but without having a primary key to link the secondary table, there is no chance of updating ie allowing data entry on the second table.

It works with static created table ie generated by the union but would be nice and clean and my OCD would be eased by simply keeping it live and I think parametric management is always better.

The union query is of course simple...cut can the primary key be allocated here? ProdID is unique across all tables. Currently Cat_Code and Item_Code are a composite primary key in supporting tables

SELECT DataSheet_Products_NBC.Cat_Family, DataSheet_Products_NBC.Cat_Code, DataSheet_Products_NBC.Cat_Name, DataSheet_Products_NBC.ProdID, DataSheet_Products_NBC.Item_Code, DataSheet_Products_NBC.Item_Desc, DataSheet_Products_NBC.Unit
FROM DataSheet_Products_NBC
UNION 
SELECT DataSheet_Estimating_Statistics.Cat_Family, DataSheet_Estimating_Statistics.Cat_Code, DataSheet_Estimating_Statistics.Cat_Name, DataSheet_Estimating_Statistics.ProdID, DataSheet_Estimating_Statistics.Item_Code, DataSheet_Estimating_Statistics.Item_Desc, DataSheet_Estimating_Statistics.Unit
FROM DataSheet_Estimating_Statistics
UNION SELECT  DataSheet_Products_Other.Cat_Family, DataSheet_Products_Other.Cat_Code, DataSheet_Products_Other.Cat_Name, DataSheet_Products_Other.ProdID, DataSheet_Products_Other.Item_Code, DataSheet_Products_Other.Item_Desc, DataSheet_Products_Other.Unit
FROM DataSheet_Products_Other;

I need to end up with a query, containing the union query, with primary key and a data table that can accept data ie be updateable.

fDatasheet view

Design

Upvotes: 0

Views: 617

Answers (1)

Siyon DP
Siyon DP

Reputation: 514

You can't Insert a new row directly to UNION query
So You will show the UNION on a subform
Then put Text controls below to enter the new row
Table Name, Cat_Family, Cat_Code, Cat_Name, ProdID, Item_Code, Item_Desc, Unit
Add a button to Insert the new row
Requery the subform to view it

Upvotes: 1

Related Questions