Would this be a query?

[MarcusW.]

SAMPLE DATABASE is ATTACHED for clarity.

When a "mechanic" is added, I need for the SKILLSET table to automatically assign a LEVEL_ID field value of "1" for every COMPONENT_ID found in the COMPONENT table.



In english:

Basically, I need to know how skilled every mechanic is for every component. The default value should be the lowest level "Level_ID"=1, until I can go in and update their individual skills with real data.


I have 30 mechanics to enter into the database, and I'm trying to avoid assigning EACH component to EACH mechanic every time I add a mechanic (or component) to the database.

Please advise or point me in the direction to where I could find an answer.

I hope this was clear. In case its not obvious, I'm new to this.

(Addtionally; if you can also answer this, it'd be helpful)

I would like to avoid being able to enter the same MECHANIC_ID + Component_ID within the SKILLSET table, more than once (preventing duplicates). *this one has me stumped also*

I'm kind of in the process of starting my form creation, and these two issues are obviously problematic.

Thanks for your help!

[ranman256]

TSkillset table
MECHANIC_ID (keyed)
Component_ID. (Keyed),
Level.

the 2 keys will prevent duplicates. So you MUST do it for every mechanic.

[MarcusW.]

TSkillset table, can only have 1 primary key.


As it is, TSkillset_table will allow:
SkillsetTable_ID, FSE_ID, Component_ID, Level_ID

101, 1,1,1
102, 1,1,1
103, 1,1,1
104, 1,1,1


I want to prevent this.
I want;
If FSE_ID + Component_ID exists, then don't allow it to be added again.

101, 1,1,1
102, 1,2,1
103, 1,3,1
104, 1,4,1


thanks!

[John_G]

You need to put a unique compound index on FSE_ID + Component_ID

[MarcusW.]

OMG! it worked!

You folks are awesome! Thank you Thank you Thank you!

Worked exactly as needed.


But back to the other question, I'm not quite clear on the answer. It sounds as if I'm being told , there is "no shortcut" to this issue.

Here it is repeated:


I have 30 mechanics to enter into the database.

Once added, I need to default to "1" for his Level_ID for each Component_ID found in the ComponentTable:
Fields: SkillsetTable_ID, Mechanic_ID, Component_ID, Level_ID

New Mechanic added= 201
101, 201, 1, 1
102, 201, 2, 1
103, 201, 3, 1
104, 201, 4, 1
105, 201, 5, 1

You see; Each mechanic MUST be rated for EACH Component. So as soon as a MECHANIC is added, he should default to a rating for ALL components. i can then go in and change their rating to the true value as needed.

As it is now, I must add a Mechanic_ID, and then ADD each Component_ID to each Mechanic_ID, and then ADD a LEVEL_ID to each Component_ID.

I want to Add a Mechanic_ID, and automatically ALL Component_ID's be added with a default LEVEL_ID=1.


Thank you!!!!!

[aytee111]

The default for the LEVEL_ID can be set on the table itself, that is an easy way to solve that problem.

Then, whenever you add a new mechanic run an append query that adds a record for each component using that mechanic ID.