Can I set PK inside my union query?

[niloufar]

I have this query which the result will be inserted into a table with Primary key to enforce the integrity rule.
I was wonder if there is a way to design this query the way to apply integrity rule PK for [StudentID] to my query without having another table TO make it Updateable?

""If you wonder why? becase my original exported files ( excel tables ) are a mess and they have too many problems and even my union query doesn't drop duplicates! ""

INSERT INTO tblStudent ( [Student ID], [Student Name] )
SELECT DISTINCT [tblStdList].[STD ID] AS [Student ID], tblStudent.tblStdList.Name AS [Student Name]
FROM (SELECT tblStdList.[STD ID], tblStdList.Name
FROM tblStdList
GROUP BY tblStdList.[STD ID], tblStdList.Name

Union

SELECT tblSkillSoft.Username, tblSkillSoft.[Student Name]
FROM tblSkillSoft
GROUP BY tblSkillSoft.Username, tblSkillSoft.[Student Name]

)  AS tblStudent
ORDER BY [tblStdList].[STD ID];

[June7]

Have you attempted this query? Does it actually work? I don't see how it can but then never tried to INSERT SELECT UNION. Username is synonymous with [Student ID]?

[Student ID] is primary key field in tblStudent?

The UNION query won't know that [Std ID] is going into a PK field. The INSERT will error if it attempts to save a duplicate PK.

[niloufar]

Hi June7,

This query works in access & I actually changed names just for privacy. Yes, [Student ID] in my new table is the primary key.

This is my main query That I would like to assign PK to the result I just would like to do that to make it updatable so I don't have to manually update the tblStudent...

SELECT tblStdList.[STD ID], tblStdList.Name
FROM tblStdList
GROUP BY tblStdList.[STD ID], tblStdList.Name

Union

SELECT tblSkillSoft.Username, tblSkillSoft.[Student Name]
FROM tblSkillSoft
GROUP BY tblSkillSoft.Username, tblSkillSoft.[Student Name]

Have you attempted this query? Does it actually work? I don't see how it can but then never tried to INSERT SELECT UNION. Username is synonymous with [Student ID]?

[Student ID] is primary key field in tblStudent?

The UNION query won't know that [Std ID] is going into a PK field. The INSERT will error if it attempts to save a duplicate PK.

[June7]

Are you saying the INSERT sql works?

I don't know what you mean by 'make it updatable' and 'assign a PK'. UNION query records are not editable.

Isn't the PK field [Student ID] populated with the [STD ID] values? What in the table has to be manually updated?

What names are in the query that had to be changed for privacy? Aren't you just referencing fields?

[niloufar]

I send you PM

I kind of normalized my two tables because they both are taken from different part of company. They have many errors and datatype problems.
So I created new tables for each, then I combine them with union query; however the union doesn't eliminate duplicates and I had to create another table with PK to enforce integrity rule and eliminate duplicate IDs.

BUT,
I have to take so many steps every time I need new reports unless. If it was just a query the result was update-able, but now I have to update 3 tables to get my final report. I hope I could explain....

What I basically need is to create a union query and enforce PK to my result. can I do that?

[June7]

I don't see any new info in the PM.

A UNION query will not show duplicate records. A UNION ALL will allow duplicate records. So I don't know what you mean by 'doesn't eliminate duplicates'.

I still don't understand what you mean by 'enforce PK'. A UNION query cannot create a unique identifier for each record, like an autonumber field in a table.

[hapm]

Even if it would be possible to generate such an ID, the resultset wouldn't be updateable. Access tracks a per field table source (allowing updateable joins in some cases) but doesn't track a per record source and therefor will never know what record came from what table. Therefor it will never know where to write updates or deletes to. On top of that it is not possible to add a primary key, or even a normal index to a query (as long as we don't talk about sql server based technics). Access wouldn't be able to enforce such unique indices or even hold them up to date. To reliable work with your input data you should have some procedure to fix the errors and datatype problems anyway. That should be the point where you merge the tables into one as you do it now. Question is: where does your requirement to make updates in the underling original tables come from? Do you need the originals updated somewhere else?

[niloufar]

Thank you hapm,
You answered my question. I was looking for some trick that I hoped to exist.
Yes, My imported tables will be updated occasionally as the result I have to update at least two normalized tables for those updates. So, I Think the only way is just having Macro to do the step for me

Thank you again all for being thoughtful

Even if it would be possible to generate such an ID, the resultset wouldn't be updateable. Access tracks a per field table source (allowing updateable joins in some cases) but doesn't track a per record source and therefor will never know what record came from what table. Therefor it will never know where to write updates or deletes to. On top of that it is not possible to add a primary key, or even a normal index to a query (as long as we don't talk about sql server based technics). Access wouldn't be able to enforce such unique indices or even hold them up to date. To reliable work with your input data you should have some procedure to fix the errors and datatype problems anyway. That should be the point where you merge the tables into one as you do it now. Question is: where does your requirement to make updates in the underling original tables come from? Do you need the originals updated somewhere else?

[niloufar]

Thank you June7, I guess I was hoping for some tricks

I don't see any new info in the PM.

A UNION query will not show duplicate records. A UNION ALL will allow duplicate records. So I don't know what you mean by 'doesn't eliminate duplicates'.

I still don't understand what you mean by 'enforce PK'. A UNION query cannot create a unique identifier for each record, like an autonumber field in a table.