Append query

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,

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.

"Dennis" wrote in message
...
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,

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.

"Dennis" wrote in
message
...
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


.

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

"Dennis" wrote
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.

"Dennis" wrote in
message
...
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


.

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dennis" wrote in message
...
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.

"Dennis" wrote in
message
...
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,

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dennis" wrote in
message
...
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.

"Dennis" wrote in
message
...
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


.



.