Results 1 to 2 of 2

Thread: strange query

  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: strange query

    I suppose this is a hard question and I hope you could help to find a solution,
    In a flower store there are two tables. Table1, goods properties and table2 showing the number of goods /products bought and sold in/out of the store. The store sells flowers in baskets, so the baskets are considered the unit of sales as the final product that goes out of the store. In other words each basket shows how many flowers are sold depending on the mentioned ratio:
    Table1:
    Code:
    Goodname     ratio
    Red_rose        3
    Yellow_ros      5
    Blue basket     
    Green basket
    The ratio describes how many of the flower is consumed in each basket. For example 3 red roses and 5 yellow roses are put and deducted from the store when one basket of flower is sold.
    Table2:
    Code:
    Good name     quantity     date_done
    Red_rose         50        14/3/2013
    Yellow_rose      75        14/3/2013
    Blue basket      10        16/3/2013
    Red_rose         80        18/3/2013
    Yellow_rose      65        19/3/2013
    Bluebasket       5         20/3/2013
    Green basket     8         20/3/2013
    Green basket     7         21/3/2013
    As you see 130 is the overall input of redrose, while 28 baskets are sold which will deduct the quantity of red rose.
    My question is that how can we have this query to show us all the transactions at a view?
    Query1:
    Code:
    Good name       quantity    ratio    flowerconsumed  remaining 
    
    
    Red_rose            130       3            84           46
    
    Blue basket          15                    45
    Green basket         13                    39
    
     
    Yellow_rose         140       5           140            0
    
    Blue basket         15                     75     
    Green basket        13                     65
    Query2:
    Code:
    Good name        quantity   remaining 
    Red_rose            130        46     
    Yellow_rose         140         0
    Blue basket         15          
    Green basket        13

    As you see 28 baskets are sold, and the ratio of red rose is 3, that means in each basket 3 red roses are sold. This means 28*3 = 84 red roses are sold and 46 is the remaining in the store. I don’t know how I can make the appropriate query to show the above results.
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As I see it you have at least 3 tables
    1 defines stick items (eg flowers)
    1 defines product (eg flowers & flower baskets)
    1 defines sales
    arguably you have a 4th table which defines products sold to a customer in sales

    the extra table for products allows you to map how many of each type of rose foes into the product (you'd probably want two tables to cover that one defiens the product, the other defines what gozinto the product). youd also need to define now many stock items go into each product even if you sell a single flower it will need a one to one mapping.

    you sales detail recordds how many of each type of product is sold

    when you need to create your breakdown then you query the sales to find product sold, by joining to the product table and the product comprises table you cna expand the individual sales into their constituent components
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •