How do I set a primary Key when making a table with a Query?

[SteveI]

Hello all, thanks for looking at my question. I am really quite new to Access (as you will probably tell).



I have created a table with a Make Table query which used a UNION query as the inputs.

The Table created has no primary key set and I wanted to set the primary key as the field 'Product' as this matches with another feld in a different table which I will use later. I would prefer it if this could be done as part of a query as I will carrying out this everyday on many tables and intend to make it into a Macro.

If anyone can tell me how to do this I would be greatly appreciative.

Thanks

[maximus]

To assign a field as the primary key in an exsisting table you can run the following sql from a form a Commandbutton on a form using either Docmd.RunSql or Execute method.

assumptions:
Table Name: Persons (Type your table name)
Primary Key Fields: P_id, LastName (In your case it will be products)


ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)


Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).


if his solves you problemmark the thread to be solved.

[SteveI]

Thanks Maximus, this does work for the table in question. Here is the Sql to create the table, is it possble to put the ALTER TABLE into the CREATE TABLE Query rather than havng two seperate queries. I only ask because I am repeating this on lots of tables.

SELECT UNIONQueryPrices.* INTO NewTable
FROM UNIONQueryPrices;

If so please can you tell me where I can put the ALTER TABLE statement and the syntax I need to use (I said I was new to Access).

Don't know if makes a difference but there are two fields in the table Product and Price.

Or alternatively is it possible to make one query that updates several tables with the Primary key all on the field Product?

I really appreciate your help.

[maximus]

Can you give me your table structure. i may have a solution that may be useful. cannot suggest until i get your table structure. alter and create table cannot be used together. But the Idea that i have is to create a table with all the required fields set the primary key and then Insert the values from the union query. plz send me the table structure.

[SteveI]

Not sure what you measn by Table Structure so I have attached the whole database.

The tables I wish to merge together are UNIONFirsttoTable, UNIONSecondtoTable, UNIONThirdtoTable and UNIONFourthtoTable all into the same format as the Grades table.

Thanks