Hi All,
I wanted to get some opinions if my query design is flawed or if I am going about this the right way. My numbers display correctly as of now, but I read a lot about running totals and using (DSum) which i'm not doing.
I am building a purchase order system where I have a static starting budget in several categories.
I created a few queries to puts everything together:
a -a select query for the category table based on the budget name and amount.
b -a select query for my purchase orders that shows the subtotal, estimated, and actual amounts; and groups on the category.
c -a select query that sums the subtotals, estimated, and actual amounts.
d -a select query that groups by category, then subtracts the starting totals from the subtotal, estimated, and actual amounts.
I was wondering if I am going about this wrong or not.
The below SQL code is for query [C]
The below SQL code is for query [D]
I wanted to get some opinions if my query design is flawed or if I am going about this the right way. My numbers display correctly as of now, but I read a lot about running totals and using (DSum) which i'm not doing.
I am building a purchase order system where I have a static starting budget in several categories.
I created a few queries to puts everything together:
a -a select query for the category table based on the budget name and amount.
b -a select query for my purchase orders that shows the subtotal, estimated, and actual amounts; and groups on the category.
c -a select query that sums the subtotals, estimated, and actual amounts.
d -a select query that groups by category, then subtracts the starting totals from the subtotal, estimated, and actual amounts.
I was wondering if I am going about this wrong or not.
The below SQL code is for query [C]
Code:
SELECT tblOCC.OCCName, Sum(qPurchaseRequisitionSubtotal.Subtotals) AS SumOfSubtotals, Sum(qPurchaseRequisitionSubtotal.FinalAmount) AS SumOfFinalAmount
FROM tblOCC INNER JOIN qPurchaseRequisitionSubtotal ON tblOCC.OCCName = qPurchaseRequisitionSubtotal.OCCName
GROUP BY tblOCC.OCCName;The below SQL code is for query [D]
Code:
SELECT qStartingBudgetOCCs.OCCName, qStartingBudgetOCCs.BudgetAmount, [BudgetAmount]-[SumOfSubtotals] AS Estimated, [BudgetAmount]-[SumOfFinalAmount] AS Actual
FROM qStartingBudgetOCCs LEFT JOIN [qSumEst&Actual] ON qStartingBudgetOCCs.OCCName = [qSumEst&Actual].OCCName
GROUP BY qStartingBudgetOCCs.OCCName, qStartingBudgetOCCs.BudgetAmount, [BudgetAmount]-[SumOfSubtotals], [BudgetAmount]-[SumOfFinalAmount], [qSumEst&Actual].SumOfSubtotals, [qSumEst&Actual].SumOfFinalAmount
ORDER BY qStartingBudgetOCCs.OCCName;
