Results 1 to 14 of 14
  1. #1
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9

    parts of car database - too many cars are counted - mistake in the design?

    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!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may wish to research Normalization and Entity Relationship Diagramming

    You are way too deep into sql and access at the moment. Get your tables set up. Create some test data. Identify some transactions and the expected outcomes. Test your model. Reconcile every issue (unexpected issue) and alter the model or the data until it does what you expect.

    There are videos(free) here that may help
    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

    http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

  3. #3
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    Okay, l´ll watch the videos and check it, although I thought I know how to design database. So do you think the whole database concept is wrong? Maybe you know where the error lies? I´ll check the videos meanwhile!

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No I don't think database concept is wrong. I do not know where the error lies at this time, but you are questioning the design in your post.
    None of us knows your business rules/facts and you haven't told us, so we can only makes guesses. Guessing isn't good. So you will have to tell us more about what you are trying to do. And with a few examples show us what some of your expectations from queries etc should be.

    I do not understand "layer".
    Please add some descriptions to help us understand.

  5. #5
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    Thank you orange for the response! Okay, I try to be more clearly:
    I want to make a database which shows me the respective parts of a car and the materials which the part is made of.
    I want to make a query that shows me the material which the part is made of. For instance, the part "steering wheel" in the layer "interior" (layer is the segment/area of the car (e.g. interior, motor, rear etc.) is made of 50g of steel, 100g of leather and 20g of plastics.

    Furthermore I´d like to count the materials for one part and see the material of each part (a steering wheel is made of plastics, leather and steel)

    In the end, I´d like to count all cars and their parts and see the average grams of each material in the cars.
    E.g. in 20 cars, there is a average of 100g of steel, 20g of leather and 10g of plastics.

    My problem with the design is that I don´t know where to put the weigh of each material. Every part in every car has a different weigh and is made of different materials. So the main problem is the table "parts_materials_vehicles", because I don´t know how to seperate this table.

    I don´t know about more business rules, if you got more question or know what to do let me know! thank you

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You have taken on a large and complex issue for sure. Have you done any database designs previously?,
    If the parts are all custom by manufacturer and have a variety of components, then it seems every part* manufacturer* model has to be treated separately

    Perhaps you can simply some things if you're working on some "made up" issue to solve. Even if you make a simpler model, and work through it, it may give you insight and experience to move to a more realistic situation,

    You may want to follow the videos on the Customer, Orders, OrderItems, Items and then work through with your own data.

    There is other material here
    http://www.rogersaccesslibrary.com/forum/topic238.html
    http://support.microsoft.com/?id=209534
    http://www.databaseanswers.org/data_models/index.htm
    http://www.databaseanswers.org/data_...hies/index.htm

  7. #7
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    There are no manufacturers, just the parts and the layer to which the part belongs. for example, trunk carpet belongs to layer trunk and has 50g of plastics and 10g of wool.

    Yea, i did database modelling in the past but I don´t know where my error lies in this one

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You're not adding much to the rationale for the database or how you might simplify things.
    How abut telling us about the 6000 rows of data?

  9. #9
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    Okay, the 6000 rows of data is actually the data from the master table. The lines look line that:
    Part Layer Material Vehicle Weigh
    ABS Sensor Body Steel Renault Clio 2
    ABS Sensor Body Plastic Mercedes Benz 1
    Suspension Axle Steel Mazda KIA 5
    Front righ seat Body Plastic Dodge RAM 2
    Safety Belt Body Leather Renault Clio 5
    I split them up in the different tables (vehicle, part, layer, material) but the most of the data is actually in the table material_parts_vehicles, which looks like that:
    Part Material Vehicle Weigh
    ABS Sensor Steel Renault Clio 2
    ABS Sensor Plastic Mercedes Benz 1
    Suspension Steel Mazda KIA 5
    Front righ seat Plastic Dodge RAM 2
    Safety Belt Leather Renault Clio 5

    My vehicle tables is this:


    v_id v_name
    1 Renault Clio
    2 Mercedes Benz
    3 Mazda KIA
    4 Dodge RAM
    5 Renault Clio

    When I connect the table vehicle to the table materials_parts_vehicle I get way to many counts for one part.

    I hope I could explain you my problem more clearly.

  10. #10
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    why is nobody able to really help? this is getting really frustrating for me

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show the sql for the query giving the "incorrect" count.

    We are still trying to figure out what exactly you are trying to do.
    If you met someone on the street and had to describe what you are trying to do, what would you say.

    In your vehicles table, why do you have 2 records for Renault Clio?

  12. #12
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    Hey! Thank you for the answer. This is my SQL Code for the incorrect count:

    SELECT Material.Material_Name, Count(Vehicle.Vehicle_ID) AS AmountOfVehicle_ID, Avg(Parts_Materials_Vehicle.Weight_Each) AS AverageOfWeight_Each
    FROM Material INNER JOIN (Vehicle INNER JOIN Parts_Materials_Vehicle ON Vehicle.Vehicle_ID = Parts_Materials_Vehicle.Vehicle_ID) ON Material.Material_ID = Parts_Materials_Vehicle.Material_ID
    GROUP BY Material.Material_Name;


    I tried it with
    SELECT count(distinct vehicle), avg(weight_each), material_id
    FROM parts_materials_vehicle
    GROUP BY material_id

    but this doesn´t work... i tried it with a subquery as well and this doesn´t work either

  13. #13
    Svear is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    9
    I´m sorry for the mistake with the Renault entry. Actually I wanted to say "Renault Clio 305", "Renault Clio 205", because I´m taking about the models. There are no manufactur or OEM data in my database and I don´t want to include them.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  2. Syntax mistake? or Access hates me?
    By axess_nab in forum Forms
    Replies: 14
    Last Post: 05-19-2011, 10:54 AM
  3. AutoExec Mistake
    By HawkGuru in forum Programming
    Replies: 1
    Last Post: 04-23-2011, 04:48 AM
  4. Prohibiting access to parts of a database?
    By Delta223 in forum Access
    Replies: 1
    Last Post: 01-05-2011, 07:31 AM
  5. field parts
    By DavidAlan in forum Queries
    Replies: 3
    Last Post: 09-07-2010, 01:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums