Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    8

    Unanswered: Is this the proper way to setup a multi-tier category breakdown?

    Hi! I am back with more questions! (Sorry but I've been stuck for a good 2 days now on the same problem).

    Let me explain my situation. My boss asked me to add in the Estimated cost for the project and on each Purchase Order (PO), I will have to indicate which category the PO belongs to.

    There are three branches to this category tree and I made three tables and connected them.

    tblCategory
    ID
    Category

    tblCategorySub1
    ID
    CategorySub1
    Category

    tblCategorySub2
    ID
    CategorySub1
    CategorySub2

    tblCategory is connected to tblCategorySub1 through Category and tblCategorySub1 is connected to tblCategorySub2 through CategorySub1.


    I have another table that stores all the estimate data
    Sample:
    ProjectNumber, Category, CategorySub1, CategorySub2, Cost
    1234, 1, 1, 1, $100
    1234, 1, 1, 2, $500
    1234, 1, 2, 1, $250
    1234, 2, 1, (null), $300
    .....
    (so on..)

    My PO table is setup the same way and I use a running sum query to get the total amount out of that PO. Then I use that query on other queries to get the total.

    Now, I can create a query to show the breakdown of the categories and I have no problem storing which category that my PO belongs to. The question becomes how can I link all these information together to generate a report that I can show the breakdown of the categories and next to it I have the estimated cost and next to the estimated cost, I have the actual cost from the PO information.

    Oh, there was a minor issue that I ran into while doing this. Some of the categories only goes up to tier 2 and does not have a third tier attached to it. But this was resolved with LEFT JOIN in query.

    This is what I run to get the category breakdown out from a query:

    SELECT CostCategory.Category, CostCategorySub1.CategorySub1, CostCategorySub2.CategorySub2
    FROM (CostCategory LEFT JOIN CostCategorySub1 ON CostCategory.Category = CostCategorySub1.Category) LEFT JOIN CostCategorySub2 ON CostCategorySub1.CategorySub1 = CostCategorySub2.CategorySub1;


    This is what I run to get each PO's total amount and their respecting category:

    SELECT POTotalAmount.PONumber, POTotalAmount.SubTotalAmount, POHeader.Category, POHeader.CategorySub1, POHeader.CategorySub2
    FROM Project INNER JOIN (POHeader INNER JOIN POTotalAmount ON POHeader.PONumber = POTotalAmount.PONumber) ON Project.ProjectNum = POHeader.ProjectNumber;



    Here is what I attempted to do to generate a query with the category breakdown with the associated PO cost.

    SELECT POHeader.PONumber, POTotalAmount.SubTotalAmount, CostCategory.Category, CostCategorySub1.CategorySub1, CostCategorySub2.CategorySub2
    FROM (CostCategory LEFT JOIN (CostCategorySub1 LEFT JOIN CostCategorySub2 ON CostCategorySub1.CategorySub1 = CostCategorySub2.CategorySub1) ON CostCategory.Category = CostCategorySub1.Category) INNER JOIN (Project INNER JOIN (POHeader INNER JOIN POTotalAmount ON POHeader.PONumber = POTotalAmount.PONumber) ON Project.ProjectNum = POHeader.ProjectNumber) ON CostCategory.CategoryID = POHeader.Category;

    This generates a datasheet that shows each PO attaching to multiple categories and not just 1.

    I've been stuck at this stage for 2 days and I don't even know how to incorporate the estimate table into this when this is giving me this huge mess!

    Any suggestion would help. If it is needed, I can upload the database file up so its easier for analysis.

    Thank you very much in advance for your help.
    Last edited by darkconz; 10-23-09 at 15:26.

  2. #2
    Join Date
    Sep 2009
    Posts
    8
    With some fiddling I got "some" to show up. However, the ones that are not showing up are as mentioned previously, the ones with only two tiers so the third tier would have a null value. Here is the SQL code I used to get it to work..

    SELECT qrCostCategory.Category, qrCostCategory.CategorySub1, qrCostCategory.CategorySub2, test.SubTotalAmount
    FROM qrCostCategory LEFT JOIN test ON qrCostCategory.CategorySub2 = test.CategorySub2
    ORDER BY qrCostCategory.Category, qrCostCategory.CategorySub1, qrCostCategory.CategorySub2;


    I don't know why its not working

  3. #3
    Join Date
    Sep 2009
    Posts
    8
    Nevermind. I gave up on this setup. I will try to approach this problem with a different method.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by darkconz
    Nevermind. I gave up on this setup. I will try to approach this problem with a different method.
    try this different method: Categories and Subcategories

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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