Man-to-many problem, what articles and how many is included in one vehicleID?

[Logistics]

Hi,

I have a many-to-many relationship problem that is quite hard to solve.

In my ProductionTable I have information on what date and time the production starts on what AssemblyArea and what the VehicleNumber is that is going to get produced.

On the other table I have what part number (Partno) included in each VehicleNumber and that quantity of each part number.

The two tables are linked together with a number called LinkNumber that has a many-to-many relationship because one VehicleNumber can include many different part numbers and one part number can be included in many different VehicleNumbers. You can see the relationship in the picture below.

In the picture below you can see the problem. I have filtered on one production date, one article number and one AssemblyArea and wants to see how many of that article number is going to be used that day (Quantity) in that AssemblyArea. Table 1 shows what I get now (Wrong value). The true value should be quantity multiplied with count of VehicleNumber for each unique LinkNumber, in this case 15.

Note that the quantity can differ from LinkNumber to LinkNumber even if it´s the same Partno.

Any help is appreciated! 

Regards 

[Anonymous]

Hi,

I would say, you need to create a bridge table to have relationship many to one in order to have this right. 

Best regards

Alin Oprita

[Logistics]

I have tried that like in the picture below but I get the same problem. As far as I understand, it is just the same problem with an extra step.