Insert a record in one table & a field in another table

[SteveNapper]

I would like to enter a value/name in a table called Process into a table called Process. This bit is no problem.

Additionally I would like to enter this value/name as a field into another table. The resulting table want's to look like:

Complexity Process1 Process2 Process3, etc
Easy
Medium
Hard
Database Design

Process1, Process2, etc. are the value/name references.

After this I will manually add a description in a memo field for the Complexity for each Process

Thanks for your help

Steve

NB
I will be adding an ID field to the column

Lastly, To look up values in a query and then save the values searched for and answer I use an Append Query?
I want to look for a process and complexity, average the time, calc a cost and then save the average time / cost for that combination as a quote, generate an ID No. then re enter the ID No. to bring up the value's in the future

 

[Sawatzky]

Dear steve,

1)
I would approach your problem in another way.

having a table :

ComboID Complexity Processname processDescription

with a unique index combined index on complexity and Processname you make sure not to double entrees.

for the time average, i assume you will need doubled values so I suggest a secondary table like

ComboId Time

2)
In general you won't store queried or calculated values, you would just use them.

3)

Having to reenter an ID No , will force you to remember the ID No or to query it. The latter case leeds to the question: if I have to query it, what is the Id for? The first case leeds to the question: If a have a compi why should I have to remember an ID No, if I could query it. ;-)


kind regards
Astrid

 

[SteveNapper]

Thanks for taking the time to reply, sorry I took so long to come back. my mail box was full so I didn't get the message!

Great idea about the combined table, but hopefully the rest below will explain why the two tables are required.

I need to save the query so that I can use the specific conditions of Object and process+complexity to then plan the work in. I have another table :

JobID ProcessID ComplexityID MaterialsID Time Qty
Process, Complexity and Materials will be repeated for the number of processes required, hence the multiple entry

The first stage will be to generate a quote based on time and materials based on the above table, nothing new there. This information (time/materials) will then be used at some time in the future to plan the work into a production schedule (another table I suppose) The easiest from a programmers view would be to print the form off, showing Object, Process and complexity then re enter if the quote comes off to get the times again. This means data re entry though, not such a good thing as I'm trying to improve efficiency!

Hope that this makes sense and thanks for the help

 

[Sawatzky]

Hi Steve,

now I am going to dive into it.

When you are talking about planning in future times, are there any creteria beyond an ID to figure out which job is to be planned (perhaps creation date or due date?)

as far as I got you, you will have jobs, which consists of severals processes, each of which has a certain complexity, needs certain material and time.

When talking about the processes, is it that there is a given number of processes that are only combined to become a job. Or does each job has special processes which cannot be generalized??

let me more about the genral idea behind your problem, as IMHO you need help for the your database design.

regards Astrid.