MAKE TABLE QUERY maintain PRIMARY KEY

[taimysho0]

hello, i have a make table query, however whenever the table gets re created it removes the primary key. How do i make it maintain the primary key? thanks

[orange]

You could create the table with the primary key, and subsequently use an append query rather than Make Table query. That way you would not affect your table structure.

If you wanted to delete all your records; you could doso via a DELETE query, and then do another Append query with the proper data.

[admessing]

You need to make sure that one of the fields in your selection is the primary key. Then group by the primary key ascending.

[taimysho0]

You need to make sure that one of the fields in your selection is the primary key. Then group by the primary key ascending.

is this after i do the append query above or is this a separate way?

[admessing]

Its generally part of the fields that you select as part of the query. Can be used in all cases (that I know of). The imprtant thing is that you tell Access that the primary key needs to a part of the query AND the resulting table. Otherwise, it will just drop it.
For Example:

SELECT
Table1.ID,
Table2.Project_Name,
Table2.Project_Area,
Table3.Watershed,
Table3.Stand_No,
Table3.CoordX,
Table3.CoordY,
Table2.Photo_Year,
"C:\Documents and Settings\Desktop\Archive_Files\" & [Table2]![Photo_Year] & "\" & [Table4]![File_Base] & "N.jpg" AS IMG_North,
"C:\Documents and Settings\Desktop\Archive_Files\" & [Table2]![Photo_Year] & "\" & [Table4]![File_Base] & "E.jpg" AS IMG_East,
"C:\Documents and Settings\Desktop\Archive_Files\" & [Table2]![Photo_Year] & "\" & [Table4]![File_Base] & "S.jpg" AS IMG_South,
"C:\Documents and Settings\Desktop\Archive_Files\" & [Table2]![Photo_Year] & "\" & [Table4]![File_Base] & "W.jpg" AS IMG_West
INTO Photo_Link
FROM (Table2 INNER JOIN Table4 ON Table2.ID = Table4.ID) INNER JOIN Table3 ON Table2.ID = Table3.ID
ORDER BY Table4.ID;

This is how I query multiple tables to establish the file pathing for a photo archive. Notice the references to the ID fields?

[orange]

If you do a maketable query based on a Table with 2 fields
Id and tName, where Id is an autonumber and Primary key in the original Table, the resulting table will NOT have a Primary Key just because you include the ID field from the first Table.

You will have to create the Primary Key in a separate step.
after you run the MakeTable query.

That's the way it works in Ac2003.

[ajetrumpet]

If you do a maketable query based on a Table with 2 fields
Id and tName, where Id is an autonumber and Primary key in the original Table, the resulting table will NOT have a Primary Key just because you include the ID field from the first Table.

ditto.

No idea why that "idea" was even mentioned.

also, don't forget DDE. With that, combined with an APPEND, you can get done what you want to get done.

2 steps there. steps have to be seperate through interface, but can be automated in code.