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 is connected to tblCategorySub1 through Category and tblCategorySub1 is connected to tblCategorySub2 through CategorySub1.
I have another table that stores all the estimate data
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
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.
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;