Primary Key

[MarketAn]

Is there a way to set a primary key when writing a make table query. I don't want duplicate records in my results.

Thanks

 

[dalchri]

You can create a primary key for the table when you create it as follows with a CONSTRAINT clause:
[tt]
CREATE TABLE tblTest (Company Text(3), File Text(4), Name Text(50),
CONSTRAINT [Key] PRIMARY KEY (Company, File))
[/tt]
If you are using a make table query, however, these is no way to define the table in the same query. You could eliminate duplicates by summarizing the table on your primary key. For example:
[tt]
SELECT Company, File, MAX(Name)
INTO tblTest
FROM tblEmployees
GROUP BY Company, File
[/tt]

 

[scriverb]

I have not been able to set a primary key with a make-table query. But, you can get around that in a round about way.

Perform a make-table query using only one record. Then through VBA code create an primary index for the field in the table. Then perform an append query to add in the rest of the records.

Dim MyDB as Database
docmd.setwarnings false
docmd.OpenQuery "qryMakeTableQuery"
Set MyDB = CurrentDB
MyDB.Execute "CREATE INDEX ClientID ON tblYourTable (ClientID) WITH PRIMARY;"
docmd.OpenQuery "qryAppendQuery"
docmd.setwarnings true

This string of code sets the warnings to false, runs the make-table query to establish the new table with one record and all of the fields, creates an index called ClientID in tblYourTable on the field ClientID making it the primary which won't allow duplicates, runs the append query of the rest of the records, and finally sets the warnings back on.

This should do it for you. Let me know if you need more help.

Bob Scriver