Impossible? Or Not? (a bit urgent)

[miffe]

Greetings, (this is a bit of an urgent post, I really hope someone can help me out)I am not sure if this is possible but I would like to make the assumption that it is, that's why I am requesting a little help on solving this issue.I work for a jewelry factory which produces rings, and as you all know, rings are composed of various source materials, can have one or various diamonds, and can come in different gold colors.I have therefore 7 example tables for this problem:- models (basic product information such as ID, name, weight, karats)- colors (ID and name: 1=yellow, 2=yellow with white, 3=white with yellow, 4=white)- stones (ID, cost, importation cost, size)- materials (ID, cost)- modelColors (idModel, idColor)- modelStones (idModel, idStone)- modelMaterials (idModel, idMaterial)-> Quite simple, now, the problem is adding up the costs to produce prices... first of all I need to display the (K, qts) value of the sum of all diamonds or stones, lets say, a model can have a .10 stone and two .08 stones, so the sum of the 3 would be 0.26. In second place I have to display the price of the model which in this case would be the sum of the cost of all materials, plus the sum of the cost + importation cost of all stones. Last of all, I might need (in some cases) to display them in each color.This is about 2 SELECT's, one in which I only display the model, total stone size, and price.The next one in which I display the model, color, total stone size, and price. So in some models it would display the info for only 2 colors, in others for all 4, etc.The Database is in MySQL but I am creating the query in access as it seems to be more simple than imagining all this, even in access I can't get the result, although I managed to get to add the sizes of diamonds, but when I want to add the modelMaterials it makes a mess because lets say I have 2 materials, of $3 each, when I display the diamonds a model has, it displays both materials under each diamond and then adds them... so instead of $6 for both materials, I get $24 if I have 4 possible diamonds.I can't figure out the solution, so far I've got this query to make a sum of the stones:SELECT models.idModel, models.name, Sum(stones.size) AS BTEFROM (models INNER JOIN modelStones ON models.idModel = modelStones.idModel) INNER JOIN stones ON modelStones.idStone = stones.idStoneGROUP BY models.idModel, models.name;Thanks for any help =)= miffe =

[miffe]

I other words...What I am getting is this:

Model | BTE  | idMaterial | cost--------------------------------E-1   | 0.10 | 1          | 20E-1   | 0.10 | 2          | 50E-20  | 0.10 | 1          | 20E-20  | 0.10 | 2          | 50E-20  | 0.12 | 1          | 20E-20  | 0.12 | 2          | 50

As we can see in the table, E-1 only has one stone (0.10) and E-20 has two stones (0.10 and 0.12), both models use the same 2 materials...The problem is that I get it shown twice, once per each stone... I want the sum of it. and also the sum of the diamonds.All summed up with my method right now it comes out something like this:

Model | BTE  | Count | SumOfCost--------------------------------E-1   | 0.10 | 2     | 70E-20  | 0.22 | 4     | 140

And what I need is this:

Model | BTE  | Count | SumOfCost--------------------------------E-1   | 0.10 | 2     | 70E-20  | 0.22 | 2     | 70

Any clue? where am I stick and what should I do?:s

[miffe]

Is there anyone at all who can help me?I'm really stuck with this right now and I can't move on with anything else until I get this sorted out =(

[vijay]

Hi.. you can refer the link http://articles.techrepublic.com.com/5100-...11-5034790.html suppose this will help you..Regards,Vijay

[Yahweh]

SELECT models.idModel, models.name, Sum(stones.size) AS BTEFROM (models INNER JOIN modelStones ON models.idModel = modelStones.idModel) INNER JOIN stones ON modelStones.idStone = stones.idStoneGROUP BY models.idModel, models.name;

An inner join combines the rows of one table with all of the rows of another table. So if you have more than one row on your stones table, then you're going to return duplicate entries on your models table.A left join returns only non-null rows in the table to the left of the join operator, which means you only get one row returned per model in your models table. You obviously should use a left join instead:

SELECT models.idModel, models.name, Sum(stones.size) AS BTEFROM modelsLEFT JOIN modelStones ON (models.idModel = modelStones.idModel)LEFT JOIN stones ON modelStones.idStone = stones.idStoneGROUP BY models.idModel, models.name;