My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Can't get data from query to table
|
|
 Mar 15 2005, 11:45 AM
Post
#1
|
|
|
UtterAccess Member Posts: 37  |
I've got a very messy query that is pulling data from a number of tables and has no unique key field. I want to get this query data into a new table and just append an autonumbered key field.
When I try to insert into a table, it won't do it and gives me an error like "cannot create more than 1 autonumber field." I tried making a blank template table with all the proper fields and 1 autonumber field -- it wouldn't insert into that either. I tried copy and pasting into the table by selecting all the records in the query result...but it will only paste them into the new table if I don't have an autonumber field, which causes many of the records to be lost. ANY method of getting the data to a table will work, as long as it works. The only thing that is important is that I retain the data type "memo" because many of the fields have more than 255 chars. Exporting to Excel and then importing back into Access seems to work but that crops everything down to 255 chars which is unacceptable. If you need to see the query to help me for some reason here it is QUOTE SELECT dbo_tblActivity_Stats.Activity_Stats_NGA_ID, dbo_tblPPO.PPO_ID, dbo_tblStat_Sum_Stats.Stat_Sum_Stats_ID, dbo_tblUser.USER_LNAME, dbo_tblDEPT.DEPT_TITLE, dbo_tblPPO.PPO_Name, dbo_tblStat_Sum_Stats.Stat_Sum_Stats_Qrt, dbo_tblStat_Sum_Stats.Stat_Sum_Stats_Auth_Internal, dbo_tblActivity_Stats.Activity_Stats_Deleted, dbo_tblImpact.Impact_Desc, dbo_tblImpact.Impact_Percent_Number, dbo_tblImpact.Impact_YR5
FROM dbo_tblImpact INNER JOIN (dbo_tblStat_Sum_Stats INNER JOIN (((dbo_tblUser INNER JOIN dbo_tblActivity_Stats ON dbo_tblUser.USER_ID=dbo_tblActivity_Stats.Activity_Stats_User_ID) INNER JOIN dbo_tblDEPT ON dbo_tblUser.USER_DEPT_ID=dbo_tblDEPT.DEPT_ID) INNER JOIN dbo_tblPPO ON dbo_tblActivity_Stats.Activity_Stats_PPO_ID=dbo_tblPPO.PPO_ID) ON (dbo_tblStat_Sum_Stats.Stat_Sum_Stats_User_ID=dbo_tblUser.USER_ID) AND (dbo_tblStat_Sum_Stats.Stat_Sum_Stats_Act_Stats_ID=dbo_tblActivity_Stats.Activit y_Stats_ID)) ON dbo_tblImpact.Impact_Activity_stats_ID=dbo_tblActivity_Stats.Activity_Stats_ID WHERE (((dbo_tblActivity_Stats.Activity_Stats_Deleted) Is Null Or (dbo_tblActivity_Stats.Activity_Stats_Deleted)="f") AND ((dbo_tblImpact.Impact_YR5)>0) AND ((dbo_tblUser.USER_DELETED) Is Null Or (dbo_tblUser.USER_DELETED)="F") AND ((dbo_tblStat_Sum_Stats.Stat_Sum_Stats_Year)=#1/1/2004#)) ORDER BY dbo_tblPPO.PPO_ID, dbo_tblStat_Sum_Stats.Stat_Sum_Stats_ID, dbo_tblActivity_Stats.Activity_Stats_CPTeam_ID, dbo_tblDEPT.DEPT_ID; |
 |
 
|
|
Mar 15 2005, 11:56 AM
Post
#2
|
|
|
UtterAccess Member Posts: 37 |
Problem solved. I was able to use insert into, and then specify all the fields other than the autonumber one.
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 23rd May 2013 - 03:58 PM |