Writing Data to Table from Query

I have a table called tbl_training which stores training data. The fields in
the table are as follows:

ID (this is the primary key; data type: AutoNumber), Employee Name (Foreign
Key), Course ID (Foreign Key), Duration, Trainer, Beg Date, End Date,
Comments.

I'd like to prevent duplicate records by creating another column which would
contain a composite primary key comprised of the EmployeeName, CourseID,
Duration, and Beg Date.

My question is how do I go about accomplishing this? I want Access to
disallow the record if the composite key is a duplicate, but I'm having a
hard time figuring out how to populate the composite key, as it's based on
the data the user enters at the time the record is created.

The users feed data to tbl_training through a form which has a query
(qry_training) as its control source. I've added an additional column to
tbl_training called DupCk, and then added a column to qry_training which
concatenates the fields to make up the primary key - but this value does not
write back to the table.

Any help would be appreciated. I have a feeling that I'm going about this
in a completely incorrect manner.

Thanks,
Manuel

Manuel

No need to add a new field, just creat a unique index on the fields you want.

1. Open the table in design view
2. Click on the indexes button on the main toolbar
3. Create a new index name in the left most column
4. in the 2nd column enter each of the fields you want to include in your
composite index

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


"Manuel" wrote:

I have a table called tbl_training which stores training data. The fields in
the table are as follows:

ID (this is the primary key; data type: AutoNumber), Employee Name (Foreign
Key), Course ID (Foreign Key), Duration, Trainer, Beg Date, End Date,
Comments.

I'd like to prevent duplicate records by creating another column which would
contain a composite primary key comprised of the EmployeeName, CourseID,
Duration, and Beg Date.

My question is how do I go about accomplishing this? I want Access to
disallow the record if the composite key is a duplicate, but I'm having a
hard time figuring out how to populate the composite key, as it's based on
the data the user enters at the time the record is created.

The users feed data to tbl_training through a form which has a query
(qry_training) as its control source. I've added an additional column to
tbl_training called DupCk, and then added a column to qry_training which
concatenates the fields to make up the primary key - but this value does not
write back to the table.

Any help would be appreciated. I have a feeling that I'm going about this
in a completely incorrect manner.

Thanks,
Manuel

Thanks that worked!

"Dale Fye" wrote:

Manuel

No need to add a new field, just creat a unique index on the fields you want.

1. Open the table in design view
2. Click on the indexes button on the main toolbar
3. Create a new index name in the left most column
4. in the 2nd column enter each of the fields you want to include in your
composite index

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


"Manuel" wrote:

I have a table called tbl_training which stores training data. The fields in
the table are as follows:

ID (this is the primary key; data type: AutoNumber), Employee Name (Foreign
Key), Course ID (Foreign Key), Duration, Trainer, Beg Date, End Date,
Comments.

I'd like to prevent duplicate records by creating another column which would
contain a composite primary key comprised of the EmployeeName, CourseID,
Duration, and Beg Date.

My question is how do I go about accomplishing this? I want Access to
disallow the record if the composite key is a duplicate, but I'm having a
hard time figuring out how to populate the composite key, as it's based on
the data the user enters at the time the record is created.

The users feed data to tbl_training through a form which has a query
(qry_training) as its control source. I've added an additional column to
tbl_training called DupCk, and then added a column to qry_training which
concatenates the fields to make up the primary key - but this value does not
write back to the table.

Any help would be appreciated. I have a feeling that I'm going about this
in a completely incorrect manner.

Thanks,
Manuel