By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Need Append Query to Generate an Autonumber

P: 10
I am trying to generate some records from one table into another. The problem is that the new table has an autonumber field (PID). The PID is part of the primary key since it is possible to have duplicate records. I did not include the PID in my append query since I just assumed it would be added automatically. My append does not add any records due to a key violation, which I assume is due to the PID not being set. How do I get the PID to be autonumbered in an append query?

I tried setting the PID in the query to 10 (since the highest number on the table so far is 9). This is what my SQL looks like;

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [ISSUEDKEYS-ACCESS]
  2.     ( EMPLOYEENUMBER, KEYNUM, ISSUEDATE, 
  3.       FIRSTNAME, DEPARTMENT, CODE, 
  4.       LASTNAME, BUILDING, ROOM, PID )
  5. SELECT [KEYS-ACCESS].EMPLOYEENUMBER, 
  6.        [KEYS-ACCESS].KEYNUM, 
  7.        [KEYS-ACCESS].ISSUEDATE, 
  8.        [KEYS-ACCESS].FIRSTNAME, 
  9.        [KEYS-ACCESS].DEPARTMENT, 
  10.        [KEYS-ACCESS].CODE, 
  11.        [KEYS-ACCESS].LASTNAME, 
  12.        [KEYS-ACCESS].BUILDING, 
  13.        [KEYS-ACCESS].ROOM, 
  14.        10 AS Expr1
  15. FROM [KEYS-ACCESS];
Feb 1 '07 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,433
After trimming your code to see what it was saying :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [ISSUEDKEYS-ACCESS]
  2.     ( EMPLOYEENUMBER, KEYNUM, ISSUEDATE, 
  3.       FIRSTNAME, DEPARTMENT, CODE, 
  4.       LASTNAME, BUILDING, ROOM ID )
  5. SELECT EMPLOYEENUMBER, KEYNUM, ISSUEDATE, 
  6.        FIRSTNAME, DEPARTMENT, CODE, 
  7.        LASTNAME, BUILDING, ROOM, 10 AS Expr1
  8. FROM [KEYS-ACCESS];
I thought that it shouldn't behave as you'd thought it was. I ran some tests and found that indeed, it did not. This means you have another problem with your indexes or (more likely) with your data.
To help resolve this, it would be nice to have some MetaData posted for the two tables and an example of your data (which must have the problem you're trying to identify).
Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
Feb 2 '07 #2

P: 10
The table being appended to;

Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata">
  3. <xsd:element name="dataroot">
  4. <xsd:complexType>
  5. <xsd:choice maxOccurs="unbounded">
  6. <xsd:element ref="ISSUEDKEYS-ACCESS"/>
  7. </xsd:choice>
  8. </xsd:complexType>
  9. </xsd:element>
  10. <xsd:element name="ISSUEDKEYS-ACCESS">
  11. <xsd:annotation>
  12. <xsd:appinfo>
  13. <od:index index-name="PID" index-key="PID " primary="no" unique="yes" clustered="no"/>
  14. <od:index index-name="PrimaryKey" index-key="PID EMPLOYEENUMBER KEYNUM ISSUEDATE " primary="yes" unique="yes" clustered="no"/>
  15. </xsd:appinfo>
  16. </xsd:annotation>
  17. <xsd:complexType>
  18. <xsd:sequence>
  19. <xsd:element name="PID" od:jetType="autonumber" od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes">
  20. <xsd:simpleType>
  21. <xsd:restriction base="xsd:integer"/>
  22. </xsd:simpleType>
  23. </xsd:element>
  24. <xsd:element name="EMPLOYEENUMBER" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  25. <xsd:simpleType>
  26. <xsd:restriction base="xsd:string">
  27. <xsd:maxLength value="50"/>
  28. </xsd:restriction>
  29. </xsd:simpleType>
  30. </xsd:element>
  31. <xsd:element name="KEYNUM" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  32. <xsd:simpleType>
  33. <xsd:restriction base="xsd:string">
  34. <xsd:maxLength value="20"/>
  35. </xsd:restriction>
  36. </xsd:simpleType>
  37. </xsd:element>
  38. <xsd:element name="ISSUEDATE" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:timeInstant"/>
  39. <xsd:element name="LASTNAME" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  40. <xsd:simpleType>
  41. <xsd:restriction base="xsd:string">
  42. <xsd:maxLength value="25"/>
  43. </xsd:restriction>
  44. </xsd:simpleType>
  45. </xsd:element>
  46. <xsd:element name="FIRSTNAME" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  47. <xsd:simpleType>
  48. <xsd:restriction base="xsd:string">
  49. <xsd:maxLength value="25"/>
  50. </xsd:restriction>
  51. </xsd:simpleType>
  52. </xsd:element>
  53. <xsd:element name="DEPARTMENT" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  54. <xsd:simpleType>
  55. <xsd:restriction base="xsd:string">
  56. <xsd:maxLength value="25"/>
  57. </xsd:restriction>
  58. </xsd:simpleType>
  59. </xsd:element>
  60. <xsd:element name="CODE" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  61. <xsd:simpleType>
  62. <xsd:restriction base="xsd:string">
  63. <xsd:maxLength value="50"/>
  64. </xsd:restriction>
  65. </xsd:simpleType>
  66. </xsd:element>
  67. <xsd:element name="BUILDING" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  68. <xsd:simpleType>
  69. <xsd:restriction base="xsd:string">
  70. <xsd:maxLength value="50"/>
  71. </xsd:restriction>
  72. </xsd:simpleType>
  73. </xsd:element>
  74. <xsd:element name="ROOM" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  75. <xsd:simpleType>
  76. <xsd:restriction base="xsd:string">
  77. <xsd:maxLength value="50"/>
  78. </xsd:restriction>
  79. </xsd:simpleType>
  80. </xsd:element>
  81. <xsd:element name="REPLACELOSTKEY" od:jetType="yesno" od:sqlSType="bit" od:nonNullable="yes" type="xsd:byte"/>
  82. <xsd:element name="SECONDCOPY" od:jetType="yesno" od:sqlSType="bit" od:nonNullable="yes" type="xsd:byte"/>
  83. <xsd:element name="Signature" minOccurs="0" od:jetType="memo" od:sqlSType="ntext">
  84. <xsd:simpleType>
  85. <xsd:restriction base="xsd:string">
  86. <xsd:maxLength value="536870910"/>
  87. </xsd:restriction>
  88. </xsd:simpleType>
  89. </xsd:element>
  90. </xsd:sequence>
  91. </xsd:complexType>
  92. </xsd:element>
  93. </xsd:schema>
  94.  
  95.  
The table the data is coming from;

Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata">
  3. <xsd:element name="dataroot">
  4. <xsd:complexType>
  5. <xsd:choice maxOccurs="unbounded">
  6. <xsd:element ref="KEYS-ACCESS"/>
  7. </xsd:choice>
  8. </xsd:complexType>
  9. </xsd:element>
  10. <xsd:element name="KEYS-ACCESS">
  11. <xsd:annotation>
  12. <xsd:appinfo>
  13. <od:index index-name="PrimaryKey" index-key="EMPLOYEENUMBER KEYNUM CODE " primary="yes" unique="yes" clustered="no"/>
  14. <od:index index-name="CODE" index-key="BUILDING " primary="no" unique="no" clustered="no"/>
  15. <od:index index-name="EMPLOYEEID" index-key="EMPLOYEENUMBER " primary="no" unique="no" clustered="no"/>
  16. <od:index index-name="KEYNUM" index-key="CODE " primary="no" unique="no" clustered="no"/>
  17. <od:index index-name="KEYNUM1" index-key="KEYNUM " primary="no" unique="no" clustered="no"/>
  18. <od:index index-name="LASTNAME" index-key="LASTNAME " primary="no" unique="no" clustered="no"/>
  19. </xsd:appinfo>
  20. </xsd:annotation>
  21. <xsd:complexType>
  22. <xsd:sequence>
  23. <xsd:element name="EMPLOYEENUMBER" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  24. <xsd:simpleType>
  25. <xsd:restriction base="xsd:string">
  26. <xsd:maxLength value="50"/>
  27. </xsd:restriction>
  28. </xsd:simpleType>
  29. </xsd:element>
  30. <xsd:element name="KEYNUM" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  31. <xsd:simpleType>
  32. <xsd:restriction base="xsd:string">
  33. <xsd:maxLength value="50"/>
  34. </xsd:restriction>
  35. </xsd:simpleType>
  36. </xsd:element>
  37. <xsd:element name="DOOR" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  38. <xsd:simpleType>
  39. <xsd:restriction base="xsd:string">
  40. <xsd:maxLength value="50"/>
  41. </xsd:restriction>
  42. </xsd:simpleType>
  43. </xsd:element>
  44. <xsd:element name="LASTNAME" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  45. <xsd:simpleType>
  46. <xsd:restriction base="xsd:string">
  47. <xsd:maxLength value="50"/>
  48. </xsd:restriction>
  49. </xsd:simpleType>
  50. </xsd:element>
  51. <xsd:element name="FIRSTNAME" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  52. <xsd:simpleType>
  53. <xsd:restriction base="xsd:string">
  54. <xsd:maxLength value="50"/>
  55. </xsd:restriction>
  56. </xsd:simpleType>
  57. </xsd:element>
  58. <xsd:element name="DEPARTMENT" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  59. <xsd:simpleType>
  60. <xsd:restriction base="xsd:string">
  61. <xsd:maxLength value="50"/>
  62. </xsd:restriction>
  63. </xsd:simpleType>
  64. </xsd:element>
  65. <xsd:element name="CODE" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  66. <xsd:simpleType>
  67. <xsd:restriction base="xsd:string">
  68. <xsd:maxLength value="50"/>
  69. </xsd:restriction>
  70. </xsd:simpleType>
  71. </xsd:element>
  72. <xsd:element name="BUILDING" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  73. <xsd:simpleType>
  74. <xsd:restriction base="xsd:string">
  75. <xsd:maxLength value="50"/>
  76. </xsd:restriction>
  77. </xsd:simpleType>
  78. </xsd:element>
  79. <xsd:element name="ROOM" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  80. <xsd:simpleType>
  81. <xsd:restriction base="xsd:string">
  82. <xsd:maxLength value="50"/>
  83. </xsd:restriction>
  84. </xsd:simpleType>
  85. </xsd:element>
  86. <xsd:element name="ISSUEDATE" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  87. <xsd:simpleType>
  88. <xsd:restriction base="xsd:string">
  89. <xsd:maxLength value="50"/>
  90. </xsd:restriction>
  91. </xsd:simpleType>
  92. </xsd:element>
  93. <xsd:element name="RETURNDATE" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  94. <xsd:simpleType>
  95. <xsd:restriction base="xsd:string">
  96. <xsd:maxLength value="50"/>
  97. </xsd:restriction>
  98. </xsd:simpleType>
  99. </xsd:element>
  100. <xsd:element name="LOST" minOccurs="0" od:jetType="longinteger" od:sqlSType="int">
  101. <xsd:simpleType>
  102. <xsd:restriction base="xsd:integer"/>
  103. </xsd:simpleType>
  104. </xsd:element>
  105. <xsd:element name="NOTES" minOccurs="0" od:jetType="memo" od:sqlSType="ntext">
  106. <xsd:simpleType>
  107. <xsd:restriction base="xsd:string">
  108. <xsd:maxLength value="536870910"/>
  109. </xsd:restriction>
  110. </xsd:simpleType>
  111. </xsd:element>
  112. <xsd:element name="OUTOFSERVICE" od:jetType="yesno" od:sqlSType="bit" od:nonNullable="yes" type="xsd:byte"/>
  113. <xsd:element name="ISSUEDATE2" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  114. <xsd:simpleType>
  115. <xsd:restriction base="xsd:string">
  116. <xsd:maxLength value="50"/>
  117. </xsd:restriction>
  118. </xsd:simpleType>
  119. </xsd:element>
  120. <xsd:element name="RETURNDATE2" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
  121. <xsd:simpleType>
  122. <xsd:restriction base="xsd:string">
  123. <xsd:maxLength value="50"/>
  124. </xsd:restriction>
  125. </xsd:simpleType>
  126. </xsd:element>
  127. </xsd:sequence>
  128. </xsd:complexType>
  129. </xsd:element>
  130. </xsd:schema>
  131.  
  132.  
  133.  
Feb 2 '07 #3

NeoPa
Expert Mod 15k+
P: 31,433
One of us is going to need to convert this into something like the example I included in my earlier post for me to work with.
I'm not offering my services ;)
Also, I need some relevant data as per my earlier post.
Feb 2 '07 #4

P: 10
I found the problem with the append query. The problem was with the table definitions, but I am not sure why. I have four fields as part of the primary key. The first one being the autonumber field. None of the other fields were listed as required or indexed. Once I changed them to Required Yes, Allow Zero Length No and Indexed Yes (Duplicates OK) the append got further. I had a date field I had to change.

I did not try different combinations of the above options. I made the all the changes on all the fields at one time.
Feb 7 '07 #5

NeoPa
Expert Mod 15k+
P: 31,433
I assume from your continued lack of response to my requests that you're no longer interested in my help. I cannot work in an information vacuum :(

NB This post was inappropriate and is only left here for reference (other posts).
Feb 7 '07 #6

P: 10
I assume from your continued lack of response to my requests that you're no longer interested in my help. I cannot work in an information vacuum :(
I am not sure why you put in this post. I posted that the problem was resolved and explained what I did to correct the problem. Also, I did communicate through PM to see if there was an easy way to get you the information requested in the proper format.

From your post you indicate that I ignored you. I might not be able to work on this issue full time so my responses are not immediate, but I was far from ignoring you. Even if that was the case, why would you post it in the thread and not in a PM?

So it is clear, I solved the problem and NO further work is needed.
Feb 7 '07 #7

NeoPa
Expert Mod 15k+
P: 31,433
I am not sure why you put in this post. I posted that the problem was resolved and explained what I did to correct the problem.
I apologise for misunderstanding (and responding inappropriately to) your latest post. Rereading it, I can no longer see why I thought it was a request for further help. My bad :(
Also, I did communicate through PM to see if there was an easy way to get you the information requested in the proper format.
...
Even if that was the case, why would you post it in the thread and not in a PM?
This is true, and I tried to explain to you why I don't use PMs to resolve questions. One of the reasons for having the questions in the thread is that it needs to be publicly viewable. That is one of the main points of the web site. I would not make myself very popular if I started dealing with a question via PM.
From your post you indicate that I ignored you. I might not be able to work on this issue full time so my responses are not immediate, but I was far from ignoring you.
This was nothing to do with your frequency of posting. I'm afraid that I misread your last post (sorry again) and assumed that you (like a fair few others unfortunately) were ignoring any request to provide further information. Clearly I was wrong in your case, but I sometimes get frustrated when members work on the basis that putting any work into their own problem is my job and somehow below them :S
So it is clear, I solved the problem and NO further work is needed.
As my earlier response should have been :
Thanks for the feedback.
It really helps when members come back and let us know the situation is sorted.
Feb 8 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.