Autonumber Issue

[brlattim]

I have a query that runs fine when its "Select". When I try and have it be a "Make Table" I get the below error: Resultant table not allowed to have more than one Autonumber field

Several tables that I have as joins in my query do have Autonumbers, but the table it creates does not. Can anyone help me identify how to solve this error?

 

[Joe4]

Can you post the SQL code of the query you are trying to run?
If you switch the query to SQL View, you can copy and paste the code here.

 

[brlattim]

Can you post the SQL code of the query you are trying to run?
If you switch the query to SQL View, you can copy and paste the code here.

INSERT INTO Routing_Eligibility ( IssueActivity, siID, IDR, ROUTED_VIA, DATE_ROUTED, FOLLOW_UP_DATE, SLA, [Routed Age], ISSUE_DESCRIPTION, resComments, resAction, rootcause, subissuereqnotes, [Routing Comments], ROUTED_TO, CM, DIR, GroupNumber, GroupName, [Eligibility Analyst], [Team Lead], IssueCategory, IssueSubCategory, DATE_COMPLETED, RETURNED, ROUTED_TO_ID, RRE, Status )
SELECT dbo_RTBL_CA_PROCESSING.IssueActivity, dbo_RTBL_CA_PROCESSING.siID, dbo_RTBL_CA_SI_ROUTING.IDR, dbo_RTBL_CA_SI_ROUTING.ROUTED_VIA, dbo_RTBL_CA_SI_ROUTING.DATE_ROUTED, dbo_RTBL_CA_SI_ROUTING.FOLLOW_UP_DATE, dbo_RTBL_ROUTED_TO_DEPARTMENTS.Follow_up AS SLA, workingdays2([date_routed],IIf([date_returned] Is Null,Now(),[date_returned])) AS [Routed Age], dbo_RTBL_CA_PROCESSING.ISSUE_DESCRIPTION, dbo_RTBL_CA_PROCESSING.resComments, dbo_RTBL_CA_PROCESSING.resAction, dbo_RTBL_CA_PROCESSING.rootcause, dbo_RTBL_CA_PROCESSING.subissuereqnotes, dbo_RTBL_CA_SI_ROUTING.Comments AS [Routing Comments], dbo_RTBL_ROUTED_TO_DEPARTMENTS.ROUTED_TO, dbo_RTBL_USERS.eeName AS CM, dbo_RTBL_USERS_1.eeName AS DIR, dbo_RTBL_GROUPNUMBERS.GroupNumber, dbo_RTBL_GROUPS.GroupName, EEA_Assignments.[Eligibility Analyst], EEA_Assignments.[Team Lead], dbo_RTBL_CA_PROCESSING.IssueCategory, dbo_RTBL_CA_PROCESSING.IssueSubCategory, dbo_RTBL_CA_PROCESSING.DATE_COMPLETED, dbo_RTBL_CA_SI_ROUTING.RETURNED, dbo_RTBL_CA_SI_ROUTING.ROUTED_TO_ID, dbo_RTBL_USERS.RRE, dbo_RTBL_CA_PROCESSING.Status
FROM (((dbo_RTBL_ROUTED_TO_DEPARTMENTS INNER JOIN dbo_RTBL_CA_SI_ROUTING ON dbo_RTBL_ROUTED_TO_DEPARTMENTS.ID = dbo_RTBL_CA_SI_ROUTING.ROUTED_TO_ID) INNER JOIN dbo_RTBL_CA_PROCESSING ON dbo_RTBL_CA_SI_ROUTING.siID = dbo_RTBL_CA_PROCESSING.siID) INNER JOIN (((dbo_RTBL_GROUPNUMBERS INNER JOIN dbo_RTBL_GROUPS ON dbo_RTBL_GROUPNUMBERS.GroupID = dbo_RTBL_GROUPS.ID) INNER JOIN dbo_RTBL_MASTER_MAIN ON dbo_RTBL_GROUPNUMBERS.ID = dbo_RTBL_MASTER_MAIN.GroupNumberID) INNER JOIN (dbo_RTBL_USERS INNER JOIN dbo_RTBL_USERS AS dbo_RTBL_USERS_1 ON dbo_RTBL_USERS.LV1_MGR_ID = dbo_RTBL_USERS_1.ID) ON dbo_RTBL_MASTER_MAIN.ASSIGN_TO_ID = dbo_RTBL_USERS.ID) ON dbo_RTBL_CA_PROCESSING.ID = dbo_RTBL_MASTER_MAIN.ID) LEFT JOIN EEA_Assignments ON dbo_RTBL_GROUPNUMBERS.GroupNumber = EEA_Assignments.[Converted Policy]
WHERE (((dbo_RTBL_CA_PROCESSING.DATE_COMPLETED) Is Null) AND ((dbo_RTBL_CA_SI_ROUTING.RETURNED)=0) AND ((dbo_RTBL_CA_SI_ROUTING.ROUTED_TO_ID)=8 Or (dbo_RTBL_CA_SI_ROUTING.ROUTED_TO_ID)=168 Or (dbo_RTBL_CA_SI_ROUTING.ROUTED_TO_ID)=180) AND ((dbo_RTBL_USERS.RRE)=1) AND ((dbo_RTBL_CA_PROCESSING.Status)<>10))
GROUP BY dbo_RTBL_CA_PROCESSING.IssueActivity, dbo_RTBL_CA_PROCESSING.siID, dbo_RTBL_CA_SI_ROUTING.IDR, dbo_RTBL_CA_SI_ROUTING.ROUTED_VIA, dbo_RTBL_CA_SI_ROUTING.DATE_ROUTED, dbo_RTBL_CA_SI_ROUTING.FOLLOW_UP_DATE, dbo_RTBL_ROUTED_TO_DEPARTMENTS.Follow_up, workingdays2([date_routed],IIf([date_returned] Is Null,Now(),[date_returned])), dbo_RTBL_CA_PROCESSING.ISSUE_DESCRIPTION, dbo_RTBL_CA_PROCESSING.resComments, dbo_RTBL_CA_PROCESSING.resAction, dbo_RTBL_CA_PROCESSING.rootcause, dbo_RTBL_CA_PROCESSING.subissuereqnotes, dbo_RTBL_CA_SI_ROUTING.Comments, dbo_RTBL_ROUTED_TO_DEPARTMENTS.ROUTED_TO, dbo_RTBL_USERS.eeName, dbo_RTBL_USERS_1.eeName, dbo_RTBL_GROUPNUMBERS.GroupNumber, dbo_RTBL_GROUPS.GroupName, EEA_Assignments.[Eligibility Analyst], EEA_Assignments.[Team Lead], dbo_RTBL_CA_PROCESSING.IssueCategory, dbo_RTBL_CA_PROCESSING.IssueSubCategory
HAVING (((dbo_RTBL_ROUTED_TO_DEPARTMENTS.ROUTED_TO)<>"Eligibility - STREAM"))
ORDER BY workingdays2([date_routed],IIf([date_returned] Is Null,Now(),[date_returned])) DESC;

 

[Joe4]

And you are sure that dbo_RTBL_CA_PROCESSING.siID is not an Autonumber field?

Are any fields listed anywhere in this query AutoNumber fields (regardless of where they are used)?

 

[brlattim]

And you are sure that dbo_RTBL_CA_PROCESSING.siID is not an Autonumber field?

Are any fields listed anywhere in this query AutoNumber fields (regardless of where they are used)?

IDR field is an autonumber. Still learning access, but not sure why it won't work in make but will in select. The table its writing to does not have any autonumber formatted fields.

 

[Joe4]

The table its writing to does not have any autonumber formatted fields.

OK, so you really are not doing a "Make Table" query (which makes a brand new table which does not currently exist), but rather you are doing an "Append Query", which writes records into an existing table.

So, in this table you are writing your records to, what kind of field is siID. Is it an Autonumber field? What is its Data Type?

 

[xenou]

It's odd that the fields in your select query are not all in the group by clause. That may be another problem. It may be the same problem - sometimes error messages are not reliable (they don't describe the actual error).

I hope you understand your query because it's probably very hard for anyone else to. Too many joins