Append query

[Dennis]

I am trying to append records from a table called Bus to a
table called Employee Records. In both of these tables the
field names are the same. In Bus, there is an autonumber
pk and in Employee Records the Employee ID is the pk. Both
tables have Employee Id fields and Bus has duplicates thus
the autonumber.

When I try to append only the field named Unit from Bus to
Employee Records, I get the following message:

MicroSoft cannot append all of the records in the append
query. 0 set to Null, 0 due to lock violations, and 1462
do to validation rule violations. In other append queries
I have made some records do not append as they are
duplicates and those error out. I know that all of these
records are not duplicates.

Below is the SQL for the Append query:

INSERT INTO [EMPLOYEE RECORDS] ( UNIT )
SELECT BUS.UNIT
FROM BUS
WHERE (((BUS.RC2)="RC"));

What might be the problem?

Thanks,

 

[Allen Browne]

As well as the Validation Rule on the field and on the table, this violation
can be triggered by things such as:

- some field has the Required property set to Yes, but you did not provide a
value for that field;

- a field's Allow Zero Length property is set to No (as it should be), but
you are trying to assign a zero-length string;

- a related record is required in another field;

 

[Dennis]

Allen,

I have looked at both tables, and in Employee Records most
fields have no constraints except the following: Employee
ID is pk & indexed Duplicates Ok, Housing indexed
Duplicates Ok.

In Bus, none of the fields except the following have
constraints: Autonumber pk & indexed No duplicates,
Housing indexed Duplicates Ok.

These two tables are joined at Employee ID and are one to
many all Employee Records and only records that match in
Bus.

I still cannot figure out why the append will not work?
Any other ideas?

Thanks,

Dennis

-----Original Message-----
As well as the Validation Rule on the field and on the table, this violation
can be triggered by things such as:

- some field has the Required property set to Yes, but you did not provide a
value for that field;

- a field's Allow Zero Length property is set to No (as it should be), but
you are trying to assign a zero-length string;

- a related record is required in another field;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to append records from a table called Bus to a
table called Employee Records. In both of these tables the
field names are the same. In Bus, there is an autonumber
pk and in Employee Records the Employee ID is the pk. Both
tables have Employee Id fields and Bus has duplicates thus
the autonumber.

When I try to append only the field named Unit from Bus to
Employee Records, I get the following message:

MicroSoft cannot append all of the records in the append
query. 0 set to Null, 0 due to lock violations, and 1462
do to validation rule violations. In other append queries
I have made some records do not append as they are
duplicates and those error out. I know that all of these
records are not duplicates.

Below is the SQL for the Append query:

INSERT INTO [EMPLOYEE RECORDS] ( UNIT )
SELECT BUS.UNIT
FROM BUS
WHERE (((BUS.RC2)="RC"));

What might be the problem?

Thanks

.

 

[Gary Walter]

Hi Dennis,

PMFBI

How can [Employee Records].EmployeeID
be the pk AND "Duplicates OK"?

The (loose) definition of pk is that "this field
will define a distinct record in the table."

If Employee ID is the pk and not autonumber,
then you will have to provide a distinct
number for it in your append query.

Else, you break validation rule.

Apologies again for butting in.

Gary Walter

Allen,

I have looked at both tables, and in Employee Records most
fields have no constraints except the following: Employee
ID is pk & indexed Duplicates Ok, Housing indexed
Duplicates Ok.

In Bus, none of the fields except the following have
constraints: Autonumber pk & indexed No duplicates,
Housing indexed Duplicates Ok.

These two tables are joined at Employee ID and are one to
many all Employee Records and only records that match in
Bus.

I still cannot figure out why the append will not work?
Any other ideas?

Thanks,

Dennis

-----Original Message-----
As well as the Validation Rule on the field and on the table, this violation
can be triggered by things such as:

- some field has the Required property set to Yes, but you did not provide a
value for that field;

- a field's Allow Zero Length property is set to No (as it should be), but
you are trying to assign a zero-length string;

- a related record is required in another field;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to append records from a table called Bus to a
table called Employee Records. In both of these tables the
field names are the same. In Bus, there is an autonumber
pk and in Employee Records the Employee ID is the pk. Both
tables have Employee Id fields and Bus has duplicates thus
the autonumber.

When I try to append only the field named Unit from Bus to
Employee Records, I get the following message:

MicroSoft cannot append all of the records in the append
query. 0 set to Null, 0 due to lock violations, and 1462
do to validation rule violations. In other append queries
I have made some records do not append as they are
duplicates and those error out. I know that all of these
records are not duplicates.

Below is the SQL for the Append query:

INSERT INTO [EMPLOYEE RECORDS] ( UNIT )
SELECT BUS.UNIT
FROM BUS
WHERE (((BUS.RC2)="RC"));

What might be the problem?

Thanks

.

 

[Allen Browne]

To debug this, create a copy of your original table (data and structure).
Try appending to the copy. Delete fields and/or delete data until you
identify the cause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have looked at both tables, and in Employee Records most
fields have no constraints except the following: Employee
ID is pk & indexed Duplicates Ok, Housing indexed
Duplicates Ok.

In Bus, none of the fields except the following have
constraints: Autonumber pk & indexed No duplicates,
Housing indexed Duplicates Ok.

These two tables are joined at Employee ID and are one to
many all Employee Records and only records that match in
Bus.

I still cannot figure out why the append will not work?
Any other ideas?

Thanks,

Dennis

-----Original Message-----
As well as the Validation Rule on the field and on the table, this violation
can be triggered by things such as:

- some field has the Required property set to Yes, but you did not provide a
value for that field;

- a field's Allow Zero Length property is set to No (as it should be), but
you are trying to assign a zero-length string;

- a related record is required in another field;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to append records from a table called Bus to a
table called Employee Records. In both of these tables the
field names are the same. In Bus, there is an autonumber
pk and in Employee Records the Employee ID is the pk. Both
tables have Employee Id fields and Bus has duplicates thus
the autonumber.

When I try to append only the field named Unit from Bus to
Employee Records, I get the following message:

MicroSoft cannot append all of the records in the append
query. 0 set to Null, 0 due to lock violations, and 1462
do to validation rule violations. In other append queries
I have made some records do not append as they are
duplicates and those error out. I know that all of these
records are not duplicates.

Below is the SQL for the Append query:

INSERT INTO [EMPLOYEE RECORDS] ( UNIT )
SELECT BUS.UNIT
FROM BUS
WHERE (((BUS.RC2)="RC"));

What might be the problem?

Thanks

.

 

[Dennis]

Allen,

I was confused I needed an Update Query. No wonder the
Append Query was not cooperating!

Thanks for your help,

Dennis

-----Original Message-----
To debug this, create a copy of your original table (data and structure).
Try appending to the copy. Delete fields and/or delete data until you
identify the cause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have looked at both tables, and in Employee Records most
fields have no constraints except the following: Employee
ID is pk & indexed Duplicates Ok, Housing indexed
Duplicates Ok.

In Bus, none of the fields except the following have
constraints: Autonumber pk & indexed No duplicates,
Housing indexed Duplicates Ok.

These two tables are joined at Employee ID and are one to
many all Employee Records and only records that match in
Bus.

I still cannot figure out why the append will not work?
Any other ideas?

Thanks,

Dennis

-----Original Message-----
As well as the Validation Rule on the field and on the table, this violation
can be triggered by things such as:

- some field has the Required property set to Yes, but you did not provide a
value for that field;

- a field's Allow Zero Length property is set to No (as it should be), but
you are trying to assign a zero-length string;

- a related record is required in another field;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I am trying to append records from a table called Bus to a
table called Employee Records. In both of these

tables
the

field names are the same. In Bus, there is an autonumber
pk and in Employee Records the Employee ID is the pk. Both
tables have Employee Id fields and Bus has duplicates thus
the autonumber.

When I try to append only the field named Unit from

Bus
to

Employee Records, I get the following message:

MicroSoft cannot append all of the records in the append
query. 0 set to Null, 0 due to lock violations, and 1462
do to validation rule violations. In other append queries
I have made some records do not append as they are
duplicates and those error out. I know that all of these
records are not duplicates.

Below is the SQL for the Append query:

INSERT INTO [EMPLOYEE RECORDS] ( UNIT )
SELECT BUS.UNIT
FROM BUS
WHERE (((BUS.RC2)="RC"));

What might be the problem?

Thanks


.

.