CODEWITHSUNDEEP
ms-access-2010
BlackEagle
BlackEagle

Reputation: 143

How to extract unique records(rows) from a table in MS ACCESS and save them to different tables

I am working in a project in MS ACCESS 2010 where i have a existing tables lets assume employee details and i have to extract each row based on the field first_name of employee and then make a different table for each of them.For example if first name is John then i should get all the records from employee details table where first name field is John and save all these rows in a new table i.e. new table_John and so on. For this i have created a new table with only unique records of first name field and then running the below code, but i am getting an run time error 3067 : Query input must contain at least one table or query

Private Sub cmdRemoveDuplicates_Click()
Dim db As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim tbl As Table

Set db = CurrentDb
Set rst = db.OpenRecordset("tblDetails", dbOpenDynaset, dbSeeChanges)
'Make new table with unique records from first name field 
CurrentDb.Execute "SELECT distinct first_name INTO [unique_records] from tblDetails;"

Set rst1 = db.OpenRecordset("unique_records", dbOpenDynaset, dbSeeChanges)
rst1.Requery
Do Until rst1.EOF

CurrentDb.Execute "SELECT tblDetails.* INTO tblNew" & rst1!first_name & _
" FROM unique_records LEFT JOIN tblDetails ON unique_records.first_name = tblDetails.first_name" & _
" WHERE (((tblDetails.first_name)='" & rst1!first_name & "'));"



rst1.MoveNext

Loop
rst1.Close
rst.Close
db.Close
Set rst1 = Nothing
Set rst = Nothing
Set db = Nothing

End Sub

Upvotes: 0

Views: 591

Answers (1)

LeeG
LeeG

Reputation: 728

My guess is you are getting a space or invalid character in there. Add [] around the table name, and if you don't want leading/training spaces, then TRIM them out.

CurrentDb.Execute "SELECT tblDetails.* INTO [tblNew" & rst1!first_name & _
"] FROM unique_records LEFT JOIN tblDetails ON unique_records.first_name = tblDetails.first_name" &  _
" WHERE (((tblDetails.first_name)='" & rst1!first_name & "'));"

If you still get an error, assign the string in the EXECUTE to a string variable, and paste it into MS Access and see what it says. Sometimes the problem is obvious when looking at the actual SQL.

Upvotes: 0

Related Questions