SQL Problem - Table 1

[ZOR]

I am trying to create a table as part of a junction scheme.

The code below extracts the top records from a table and puts it into another table. However the field AthleteID in the receiving table ends up as an Autonumber field which is what I want, however it gets stripped of being a primary key. The source table field is a primary key. Any ideas, thanks

Dim strSQL As String

DoCmd.SetWarnings False

' CLEAR AthleteNames TABLE
' Set db = DBEngine(0)(0)
' strSQL = "DELETE FROM AthleteNames;"
' db.Execute strSQL
' Set db = Nothing
' DoEvents

strSQL = "SELECT A.AthleteID, A.Athlete, A.Country, A.Other, A.SportorSports INTO AthleteNames FROM AllAthletes AS A"
strSQL = strSQL & " WHERE (((A.AthleteID) In (Select top 1 B.AthleteID"
strSQL = strSQL & " from AllAthletes as B where B.Athlete = A.Athlete ORDER"
strSQL = strSQL & " BY B.AthleteID)))"
strSQL = strSQL & "ORDER BY A.Athlete;"

DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

 

[dhookom]

You can't create indexes with a maketable query. Can you use delete and append queries with the same result?

Duane
Hook'D on Access
MS Access MVP

 

[ZOR]

Many thanks Duane,

That did it. Have a star and good weekend. Regards