Here is my logic behind the design: one car has many areas (rear, trunk) and one area can belong to many cars
One area has many parts, whereby one part belongs to only one area in the car (trunk carpet belongs to area trunk)
One parts is made out of many materials, but one material can included in many parts.
The n:M table between materials and parts has the vehicle_id and the weigh of a part included to be clearly identified.
Now I´ve got some problem: If I connect the vehicle table with the Parts_materials_vehicle table and count the vehicles, I get a number ouf 3000 vehicles! (but there are only 20).
Furthermore I want to see the average amount of the respective material in all vehicles.
This is my select:
SELECT Material.Material_Name, Count(Vehicle.Vehicle_ID) AS AnzahlvonVehicle_ID, Avg(Parts_Materials_Vehicle.Weight_Each) AS MittelwertvonWeight_Each
FROM Vehicle INNER JOIN (Material INNER JOIN Parts_Materials_Vehicle ON Material.Material_ID = Parts_Materials_Vehicle.Material_ID) ON Vehicle.Vehicle_ID = Parts_Materials_Vehicle.Vehicle_ID
GROUP BY Material.Material_Name;
but the result is obviously wrong because the number of cars is about 3000 as well.
I´ve got a mistake somewhere, I dont know in the design or in the SQL code.
Could someone tell me how I can prevent counting all lines in the parts_materials_vehicle database when I SELECT count(vehicles) FROM Vehicle v, Parts_Materials_Vehicle pmv WHERE V.Vehicle_ID = pmv.Vehicle_ID
As far as I can see from the database diagram, you created a cartesian product. There is something wrong with the relationships (see attachement). I'm not sure whether its only in the JOIN operations or if the problem lies in your database organisation.
Thank you for your answer! But even if I would delete the layer_vehicle table, I would still count an unrealistic amount of cars if I type
FROM materials_parts_vehicle m, vehicle.v
WHERE m.vehicle_id = v.vehicle_id
and I can´t use the SELECT count(distinct vehicle_id) in access.
Maybe the error lies in either the materials_parts_vehicle or vehicle layer?