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; - INSERT INTO [ISSUEDKEYS-ACCESS]
-
( EMPLOYEENUMBER, KEYNUM, ISSUEDATE,
-
FIRSTNAME, DEPARTMENT, CODE,
-
LASTNAME, BUILDING, ROOM, PID )
-
SELECT [KEYS-ACCESS].EMPLOYEENUMBER,
-
[KEYS-ACCESS].KEYNUM,
-
[KEYS-ACCESS].ISSUEDATE,
-
[KEYS-ACCESS].FIRSTNAME,
-
[KEYS-ACCESS].DEPARTMENT,
-
[KEYS-ACCESS].CODE,
-
[KEYS-ACCESS].LASTNAME,
-
[KEYS-ACCESS].BUILDING,
-
[KEYS-ACCESS].ROOM,
-
10 AS Expr1
-
FROM [KEYS-ACCESS];
7  5959  NeoPa 32,567
Recognized Expert Moderator MVP
After trimming your code to see what it was saying : - INSERT INTO [ISSUEDKEYS-ACCESS]
-
( EMPLOYEENUMBER, KEYNUM, ISSUEDATE,
-
FIRSTNAME, DEPARTMENT, CODE,
-
LASTNAME, BUILDING, ROOM ID )
-
SELECT EMPLOYEENUMBER, KEYNUM, ISSUEDATE,
-
FIRSTNAME, DEPARTMENT, CODE,
-
LASTNAME, BUILDING, ROOM, 10 AS Expr1
-
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 - Table Name=tblStudent
-
StudentID; Autonumber; PK
-
Family; String; FK
-
Name; String
-
University; String; FK
-
MaxMark; Numeric
-
MinMark; Numeric
The table being appended to; -
<?xml version="1.0" encoding="UTF-8"?>
-
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata">
-
<xsd:element name="dataroot">
-
<xsd:complexType>
-
<xsd:choice maxOccurs="unbounded">
-
<xsd:element ref="ISSUEDKEYS-ACCESS"/>
-
</xsd:choice>
-
</xsd:complexType>
-
</xsd:element>
-
<xsd:element name="ISSUEDKEYS-ACCESS">
-
<xsd:annotation>
-
<xsd:appinfo>
-
<od:index index-name="PID" index-key="PID " primary="no" unique="yes" clustered="no"/>
-
<od:index index-name="PrimaryKey" index-key="PID EMPLOYEENUMBER KEYNUM ISSUEDATE " primary="yes" unique="yes" clustered="no"/>
-
</xsd:appinfo>
-
</xsd:annotation>
-
<xsd:complexType>
-
<xsd:sequence>
-
<xsd:element name="PID" od:jetType="autonumber" od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:integer"/>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="EMPLOYEENUMBER" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="50"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="KEYNUM" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="20"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="ISSUEDATE" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:timeInstant"/>
-
<xsd:element name="LASTNAME" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="25"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="FIRSTNAME" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="25"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="DEPARTMENT" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="25"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="CODE" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="50"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="BUILDING" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="50"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="ROOM" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="50"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
<xsd:element name="REPLACELOSTKEY" od:jetType="yesno" od:sqlSType="bit" od:nonNullable="yes" type="xsd:byte"/>
-
<xsd:element name="SECONDCOPY" od:jetType="yesno" od:sqlSType="bit" od:nonNullable="yes" type="xsd:byte"/>
-
<xsd:element name="Signature" minOccurs="0" od:jetType="memo" od:sqlSType="ntext">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:string">
-
<xsd:maxLength value="536870910"/>
-
</xsd:restriction>
-
</xsd:simpleType>
-
</xsd:element>
-
</xsd:sequence>
-
</xsd:complexType>
-
</xsd:element>
-
</xsd:schema>
-
-
The table the data is coming from; NeoPa 32,567
Recognized Expert Moderator MVP
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.
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.
NeoPa 32,567
Recognized Expert Moderator MVP 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).
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.
 NeoPa 32,567
Recognized Expert Moderator MVP
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 misunderstandin g (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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Mark |
last post by:
I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the exception of a few records missing zip. A
person may be in one to five records in the database. If a person is in multiple
records, the other fields in the table in each record for that person may or may
not contain data. I have two problems:
1. I...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.
Here's the basic specs and database schema:
-------------------------------------------
|
by: tHeRoBeRtMiTcHeLL |
last post by:
Below is an earlier post to an Excel Group..
....but I thought that there might be a way to do this in Access
by importing data and then creating append and/or update query. I would
most certainly need to use the right type of table join and criteria in
the query to perform the task, and don't find myself an expert or up to
par as far as I'm concerned.
*******************************************************************
|
by: travismorien |
last post by:
I have four tables of different "entities". One table contains
information for "people", one for "trusts", one for "companies" and one
for "self managed super funds". Each type of entity has an autonumber
ID, "Person ID" "Trust ID" "Company ID" and "SMSF ID"
A "portfolio" table holds information about what shares, funds and
properties everyone owns. But because its organised by "PersonID" it
currently only can hold information for...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.
Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.
If I'm right about this, I need some suggestions as to the...
| | |
by: a_masselink |
last post by:
It doesn't append anything to the primary key (autonumber, long
integer), only to all the other fields. Even if I set all the fields to
allow zero length: yes, indexed: no and required no. All the field
names are the same in the query as in the destination table.
This is the query:
INSERT INTO tblTempPackagesImp ( PackQuantImp, PackTypeImpID,
GrossWeightImp, GrossCubeImp, CommodityName, ContainerID, BolImportID,
ContainerSealNumber,...
|
by: jnice814 |
last post by:
I am trying to create an AutoNumber that will begin at a number that I specify. I have followed all instructions in MS Access Help, but it is still not working. I continue getting an error message that 1 record has a validation rule problem, but I cannot figure out what it is. I don't have anything in the validation rule fields on either table. I've checked everything in both tables. What can I be missing? This is very frustrating...
I...
|
by: wade.wall |
last post by:
Hi all,
I am having a problem appending data to an existing table. I have two
tables with identical fields and I want to append the data from one
table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
a difference of 73 records. When I run the append query, only 72
records are selected from T2, and there doesn't seem to be a pattern
as to why the records selected were selected. They aren't the last 72
records or the...
|
by: iheartvba |
last post by:
Hi,
I have a simple append query which takes data from a form and appends it into a table.
INSERT INTO tblSmsSent ( MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate )
SELECT !! AS Expr1, !! AS Expr2, Now() AS Expr3, !! AS Expr4, !! AS Expr5
FROM tblSmsSent;
The Table has the following fields
Field Type
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
