How to merge multiple querities into sub-queries on a single query?

[securitywyrm]

Thank you everyone for your help with this project. I think I'm at the point where I can ask the right question.



I have a series of queries used for a fiscal dashboard. The main data source is a list of fund control points (FCP), and then I have a separate queries to show budget, spent, obligated, planned, etc. What I'd like to do is take each of these queries and turn them into an expression in a larger query. So for example...

SELECT TFiscalData.FCP, Sum(TFiscalData.Cost) AS DashBudgetTotal
FROM TFiscalData
WHERE (((TFiscalData.TransactionPrimary)="BUDGET"))
GROUP BY TFiscalData.FCP;

and

SELECT TFiscalData.FCP, Sum(TFiscalData.Cost) AS DashObligatedTotal
FROM TFiscalData
WHERE (((TFiscalData.TransactionPrimary)="Expense"))
GROUP BY TFiscalData.FCP, TFiscalData.PoStatus
HAVING (((TFiscalData.PoStatus)="Obligated"));

Would each be a subquery in a larger query. This would make it exponentially easier to change it later for say, changing what fiscal year it's looking at. Unfortunately I only understand the most basic of expressions, like "DashBudgetTotal: Sum(Cost)", what do I need to do to turn all that SQL into an expression?

[June7]

Why is the Obligated summation grouped by PoStatus and Budget is not?

[securitywyrm]

Why is the Obligated summation grouped by PoStatus and Budget is not?

Budget entries don't have a PO status. I'll also have some other strange ones, like cost transfers where the primary field is "Cost transfer" but the secondary will either be "Received" or "Disbursed" and thus the display field will be one minus the other.

If it helps to put this in context, this is federal government accounting.

[June7]

I don't think your vision of a single big query that is a 'merge' of multiple queries with varying grouping is realistic.

The two queries don't have the same grouping so they can't be related. If they both grouped on only FCP, then they could be either joined to each other (if one of them has all possible FCP values) or both joined to a table of unique FCP values.

[securitywyrm]

I don't think your vision of a single big query that is a 'merge' of multiple queries with varying grouping is realistic.

The two queries don't have the same grouping so they can't be related. If they both grouped on only FCP, then they could be either joined to each other (if one of them has all possible FCP values) or both joined to a table of unique FCP values.

They're all going to be grouped by FCP. My question is, what's the process to turn a query into a subquery?

[June7]

They may all include FCP as one group parameter but you already show one query that has an additional group parameter that is not shared by the other query you show.

Usually I would first build the inner query and save it. Then I build another query that references the first query to get the proper syntax. Then I copy/paste the SQL statement from the first query into the appropriate location of the second query. Delete the first query object. However, there are some nested queries where the inner statement cannot be a standalone query. In which case I simply follow an example and type the full structure into SQL View.

Here is an article with several examples of nested queries http://allenbrowne.com/subquery-01.html