add a record to a table many times 1

[MICKI0220]

I have a program that I need to edit. The primary key is "operation". It has one operation to many jobs. I need to create an add button to the form. What this button should do is add a new job to each operation, thus creating several records in a table with a "one job to many operations" records....For example, job e-6516 will combine with primary key 100 creating one record, then e-6516 will combine with primary key 110 creating another record and so on. Can anyone give me a clue as to how I should do this? I want to just have to type in the new job and click "Add".

Thanks

Micki

 

[fneily]

I would keep the key split so then you would create a multi-field primary key. Do you want to have a jump of 10 increments? Or is that just an example?
So, in the many table, have:
KeyID Job other fields
100 e-6516
101 e-6516
102 e-6516
etc.

This way KeyID could be an autonumber, if so desired. The primary key to that table is the combination of the two fields. So the PK's are
100e-6516
101e-6516
102e-6516
etc.

 

[MICKI0220]

That is just an example. This is a combination of two tables. The first table has all the operations, the second table has the job and operations combination. That is the table I want to add these records to. I am not sure if I have to code it or just create a query and then use an add button wizard....I am totally lost on even how to start it.

 

[PHV]

A starting point (SQL code):
INSERT INTO [second table] ([job],[operation])
SELECT [Forms]![your form]![your job textbox],[operation]
FROM [first table]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

You state:"It has one operation to many jobs."
Then you state:"in a table with a "one job to many operations" records"
One of these statements isn't correct OR they are both correct, a many-to-many relationship. I'm going to assume you mean one operation has many jobs. Then your table structures will look something like:
tblOperations
OperationID Primary Key
Description
other operation fields

tblJobs
JobID Primary Key (may be autonumber but I'd create one)
OperationID
Description
StartDate
EndDate
other job fields

So you just create a form/subform. Then you can use code to copy the OperationID into the subform.

 

[MICKI0220]

One table, the operations table, has a primary key of "operation". The only thing in this table are two fields, one is operation and the other is the team that operation belongs to. Another table, "standards", has a combination primary key made up of the job number and the operation. This is the table that I would like to add records to. Because a job has many operations it goes through to be completed, it takes a long time adding records individually. I would like to create an add button that takes whatever new "job number" I type into a text box, attaches it to each operation from the operations table, and creates a new record in the other table for each combo of operation and job number, that is in the operations table. For example, there are about 50 operations in the operations table. By creating a new job with this add button, I would be adding 50 new records to the standards table...Each record would have the same new job number but only one of the operations per record.

 

[PHV]

This is precisely what my suggestion is supposed to do ...

 

[MICKI0220]

Thank you Phv....I am trying it. I was just clarifying my question for fneily.