Hey guys! i´m creating an autoparts and their respective materials database.
I´ve got about 6000 data rows of 20 cars.
One row looks like that:
PartID:trunk carpet (trunk) area: trunk material: wool car: Mazda R306 Weigh: 1 pounds
This is how I designed the database in access:
http://img845.imageshack.us/img845/456/erdcar.png
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
Thank you very much!