Circular Reference Caused By Alias

[sambrierley]

hi all,

im trying to insert into a table all records with the duplicates removed but im getting the above error.

Str = "INSERT INTO [Export Table_Dupes]" & _
        "SELECT DISTINCT [Alpha Name], [Employee Number], [Work Date], [Account Number], [Explanation], [Sub- Ledger], [Pay Code], [Billing Rate], " & _
        "[Hours], [Week No], [Timesheet Identifier], [Week Ending], Comments, Approver, Timestamp, [Home BU] " & _
        "FROM [Export Table]"

basically the tables are exactly the same, the only field im not selecting from the Export table is the ID column as this obviously wont be duplicated.

id appreciate if someone could show me where im going wrong.

 

[arnelgp]

you need to insert a space between [Export Table_Dupes] and SELECT DISTINCT:

Str = "INSERT INTO [Export Table_Dupes] " & _
"SELECT DISTINCT [Alpha Name],

also you have to specify the field name in the first table:

Str = "INSERT INTO [Export Table_Dupes] (field1, field2, ...) " & _
"SELECT DISTINCT [Alpha Name], [Employee Number], [Work Date], [Account Number], [Explanation], [Sub- Ledger], [Pay Code], [Billing Rate], " & _
"[Hours], [Week No], [Timesheet Identifier], [Week Ending], Comments, Approver, Timestamp, [Home BU] " & _
"FROM [Export Table]"

 

[sambrierley]

hi,

thanks for the response.
ive made the changes as you explained to no avail, i also tried the slightly different syntax of INSERT INTO [Export Table Dupes].field

"INSERT INTO [Export Table Dupes]([Alpha Name],[Employee Number],[Work Date],[Account Number],[Explanation],[Sub- Ledger],[Pay Code],[Billing Rate],[Hours],[Week No],[Timesheet Identifier],[Week Ending],Comments,Approver,Timestamp,[Home BU]) " & _
        "SELECT DISTINCT [Alpha Name], [Employee Number], [Work Date], [Account Number], [Explanation], [Sub- Ledger], [Pay Code], [Billing Rate], " & _
        "[Hours], [Week No], [Timesheet Identifier], [Week Ending], Comments, Approver, Timestamp, [Home BU] " & _
        "FROM [Export Table]"

p.s you may have noticed my table name has changed, this was on purpose and is relflected corrrectly.

thansk

 

[arnelgp]

put an Alias on the second table:

"INSERT INTO [Export Table Dupes]([Alpha Name],[Employee Number],[Work Date],[Account Number],[Explanation],[Sub- Ledger],[Pay Code],[Billing Rate],[Hours],[Week No],[Timesheet Identifier],[Week Ending],Comments,Approver,Timestamp,[Home BU]) " & _
        "SELECT DISTINCT T1.[Alpha Name], T1.[Employee Number], T1.[Work Date], T1.[Account Number], T1.[Explanation], T1.[Sub- Ledger], T1.[Pay Code], T1.[Billing Rate], " & _
        "T1.[Hours], T1.[Week No], T1.[Timesheet Identifier], T1.[Week Ending], T1.Comments, T1.Approver, T1.Timestamp, T1.[Home BU] " & _
        "FROM [Export Table] AS T1"