Relation consist Multiple Primary key in single table

[suzeq]

Hi,

I had one issue when i was trying relate 1 table(consist multiple PK) to other table,
error message was "Indeterminate" and i even try to reorder PK index for more than 6 times but seem like i was not managed to get the solutions.

Attach is my relationship, hopefully someone can help me on this.

[RuralGuy]

Why do you feel you need a compound primary key?

[John_G]

Your table EmpProductDetails is a mistake - you don't need it. You can get any data you need regarding Product Details for an Employee through the other relationships. You get an error trying to create that new relationship because the result would be (on the diagram) two different paths from EmployeeRecord to BEProductDetails.

The Existing one is : EmployeeRecord - EmpProduct - BEProduct - BEProductDetails

The second one would be : EmployeeRecord - EmpProductDetails - BEProductDetails
The two paths are inconsistent, so you can't create the last relationship.

So as I said, get rid of EmpProductDetails and use the existing relationships.

[suzeq]

Hi RuralGuy,
What did you mean by compound primary key? sorry as i am new to this access.

Hi John_G,
Basically we have so many different products with approximate 30+ process for each products and most of the process are different (sample attachment only BEProduct and many product to come).
The main problem was each employee may be allocate to many product and be training many different process.

My main objective was, by end of the day I want to know employee training status through query by employeeID, by product or by process.

Your table EmpProductDetails is a mistake - you don't need it. You can get any data you need regarding Product Details for an Employee through the other relationships.
"I need EmpProductDetails table as my one of my objective was to know which process employee A has been train for.
"I need BEProductDetails table as one of my objective was to know employee A allocation history(ex of product name Belize,Apx etc)

So as I said, get rid of EmpProductDetails and use the existing relationships.
"I i get rid of EmpProductDetails table, there is no other table that hold Employee data+Product". Any idea?

I cant attach my database here as i cant found attachment button.

[John_G]

"I i get rid of EmpProductDetails table, there is no other table that hold Employee data+Product". Any idea?

In that case, then the one you don't need EmpProduct. You can't have both. The table EmpProductDetails can contain the training data, and be linked to EmployeeRecord and BEProductDetails. It is easy enough to get the actual Product information because BEProductDetails has the BEProductID in it.

The table BEProductDetails should not have BEProductID as part of its primary key; just BEProductDetailsID should do.

[QUOTE][(sample attachment only BEProduct and many product to come)./QUOTE]

If this means you plan to have separate tables for each product, DON'T proceed that way. Use one product table and one details table, otherwise you will find you have all sorts of problems with querying and entering information.

[RuralGuy]

Hi RuralGuy,
What did you mean by compound primary key? sorry as i am new to this access.

I cant attach my database here as i cant found attachment button.

It appears as though you have two fields as the Primary Key. That is a compound key. All you need is the AutoNumber field as the PK. The attachment button is in the Advanced area if you scroll down. Be sure and Compact and Repair and then zip it before you post it.

[suzeq]

Hi,
I have modify my table as per everyone input and managed to created relation between all my table(using 1 department out of 3).
Basically i have divided all our company products into 3 main group which are (BE, FE & Battery) since they have different many products with different processes.

I was planning to put up all 3 main product under EmpProduct table but error was prompt since employees in BEProduct table were not inside FE table.
So far my relation work out well with 1 department(BE) as per attachment and I need help/idea how to squeeze other 2 product(FE & Battery).

[John_G]

The relationship new diagram in your attachment is going to lead (I think) to inconsistencies in the data. You have several many-to-many relationships shown in there, but are they really many-to-many?

One employee can be assigned to many products. That makes sense.
But can one product have many employees allocated to it?

Similarly, one employee can be assigned to many processes, and that makes sense.
But, can one process have many employees allocated to it? That seems odd.

One product can have many processes related to it - makes sense.
But, can one process be applicable to many products? Your table ProductProcess indicates they can.

The answers to these questions will help sort out what the relationships really should look like.

[suzeq]

Hi John,

I had try to modify my table and relation based on your advise and please do review my latest database.
Attachment 24012


1) So far it was seem fine with 1 department only (BE) but when I try to added second department (Table FE) it has became chaos. Basically BE and FE have totally different process so i was divided into different table but all employee were under table EmpProductProcess. Both FEProcessID and ProcessID have same data type. Any idea what went wrong?

2) Form EmployeeDetail – EmployeeDetailsubform does not function properly.

3) Form QueryByID – when i entered employee ID, it still didn't reflect into form

4) Form QueryByIDSubform – I cant scroll the to view the record

5) FormQueryByProcess – Still working on it as my combo box still does not function

rgds

[suzeq]

oppss sorry as i missout the file . here my latest attachment