Append Query

[ardagh]

Hi

I have setup a database which captures manufacturing data and produces management reports.

i should state that I am not an access expert. I have the following tables customers, employees, operations, timesheet, workorder. The first two table are self explanatory. The operations table holds information on the operations that are relevent to each customer.

Customer OperationNo SequenceNo OperationName Target
ABB01 1 RUNNING EXTRUSION M/C 1 LINE 0
ABB01 1a RUNNING EXTRUSION M/C 1a LINE 0
ABB01 2 RUNNING EXTRUSION M/C 2 LINE 0
ABB01 3 RUNNING EXTRUSION M/C 3 LINE 0
ABB01 4 RUNNING EXTRUSION M/C 4 LINE 0
ABB01 6 RUNNING EXTRUSION M/C 6 LINE 0
ABB01 A ASSEMBLY 0
ABB01 I INSPECTION 0
ABB01 O OTHER (ENTER DETAILS IN COMMENTS) 0
ABB01 PK PACKING 0
ABB01 PW PAPERWORK 0
ABB01 T TRAINING 0
ABB01 TR TRIMMING TUBES 0

where ABB01 = customer
1,2,3,A,T etc = Operation No
Sequence No is blank
RUNNING EXTRUSION M/C 1a LINE, ASSEMBLY = Operation Name
Target is zero in all cases

A customer can have any number of operations. I have a form which allows the production management to add new operations for a customer.

I also have a form for setting up new customers. At the end of this I have a button to take the user to the add new operations form.

My Question:
Is it possible that when I set up a new customer that on saving the record a default list of operations would be appended to the operations table for this customer.

E.g. If I set up a customer ZZZ01 that the operation in the table above would be appended to the operations table for that customer. The sequence No and target fields are not important and can remain blank.

I could then amend this default list if required (These could be held in a seperate table).
I could also amend the operations table to delete or add any new operations for this customer that were not on the default list.

Long winded - I know.

Any help would be appreciated

Regards
Niall

 

[KenReay]

Hi

In the after Insert event of your form which maintains teh list of customers

Dim strSQL as String

strSQL = "INSERT INTO Operations SELECT " & Me!CustomerId & " As CustomerId, CustomerName FROM OperationsDefaults;"

docmd.runsql strSQL

You need to flesh out the SQL to include the column names in your table

The above assumes you are going to have a seperate table for the default operation.

Regards

Ken Reay

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK