Need help with the concept of a subquery, haven't written direct SQL before

[securitywyrm]

THE TLDR: I have three fields, FCP, COST, and STATUS. I need to know the subquery expression to display SUM of COST where STATUS = "Budget"

---

Long Version

I'm trying to create a fiscal dashboard (despite having no Access, SQL, or programming training) and I'm quite stuck in getting the fields to propertly interact.

For simplification, let's say I have three fields
FCP : Fund control point, where the money is coming from. It's a list of numbers.
Cost: How much was spent
Status (Budget, Spent, Obligated, or Planned)

What I tried and failed: Created a form where the data source is a list of FCP. I made a query for the relevant field (Select FCP and sum of cost where status=budget) and put that into a subform, and have a text box siaplay the output of that query in a subform. This worked, BUT... this can only display one fund control point at a time, as subforms can't be in a continuous form. My plan (before learning of this stupid restriction) was to make a subform for each of the relevant queries (One for each Status type), put them in a row, and let the continuous form display all the fund control points. This isn't going to work.

I also tried how I thought "Query by form" would work. I created a continuous form of the fund control points, and set a query to pull data where the fund control point equaled the fund control point field on the continuous form. The result was that it would pull the correct data, but ONLY for the first field, and display that same data for all subsequent fields. So if the first listed FCP was 30, it would display that field's data in the entire column of outputs.

As I understand it, what I need to do is a subquery, such as demonstated at https://msdn.microsoft.com/en-us/lib...ffice.11).aspx . In this way my data source would have all of the relevant information. As I understand it, I am cramming an entire query into a single field of a larger query.

So as I understand it, I need to create a query, activate "totals", and the first column is FCP with "Group by." Then I need to create the subqueries in the other columns for each status. So it would be something like this

First Column: FCP (Group by)
Second Column: BudgetEntry: (SELECT Sum(Cost) FROM TFiscalData WHERE Status = "BUDGET") (Expression)
This results in the same thing as when I trid Query by Form, with all of the results being the result for the first displayed field. What I think is happening is that the expression needs some sort of "Where FCP equals" but I don't know where to go from here. As I understand it, that the query is already grouped by FCP means that shouldn't be a part of the query.

I want this query to spit back the sum of COST where STATUS equals "Budget", sorted by FCP. Please help.

[CJ_London]

this

Second Column: BudgetEntry: (SELECT Sum(Cost) FROM TFiscalData WHERE Status = "BUDGET") (Expression)

wont work if TFiscalData is also in your main query - SQL will just assume it is the same table.

To resolve, you need to alias it - as anything you like. Most would use T or Tmp

BudgetEntry: (SELECT Sum(Cost) FROM TFiscalData AS T WHERE Status = "BUDGET")

[securitywyrm]

this

Second Column: BudgetEntry: (SELECT Sum(Cost) FROM TFiscalData WHERE Status = "BUDGET") (Expression)

wont work if TFiscalData is also in your main query - SQL will just assume it is the same table.

To resolve, you need to alias it - as anything you like. Most would use T or Tmp

BudgetEntry: (SELECT Sum(Cost) FROM TFiscalData AS T WHERE Status = "BUDGET")

Still gives me the same number for every fund control point. Here's the actual code I used

BudgetEntry: (SELECT Sum(cost) FROM TFiscalData As TempBudget WHERE TransactionPrimary = "Budget")

Now since this is a "totals" query, and I have it grouped by FCP (fund control point), it should be giving me the sum of the budget costs divided by fund control point. Do I need to add a "group by" command somewhere in the subquery?

Subqueries are currently kicking my ass

[pbaldy]

Is there a reason a simple totals query won't work? Your subquery would need FCP in the criteria, but I don't see why you need a subquery. If I'm wrong try:

BudgetEntry: (SELECT Sum(cost) FROM TFiscalData As TempBudget WHERE TransactionPrimary = "Budget" AND FCP = " & FCP)

[pbaldy]

You might try

SELECT FCP, Sum(Cost) As HowMuch
FROM TFiscalData
WHERE TransactionPrimary = "Budget"
GROUP BY FCP

[securitywyrm]

You might try

SELECT FCP, Sum(Cost) As HowMuch
FROM TFiscalData
WHERE TransactionPrimary = "Budget"
GROUP BY FCP

That worked, but as its own query instead of a subquery. What I'm trying now is to build a separate query for each field, and then use relationships in the data source for the form to link them together. However there's a new issue.

THe budget query works, BUT... it only shows 9 of the 17 FCP, because only 9 of them have budgets. That's a problem, as I need to show other data for these fields as well. How can I get budget to display as 0 if there are no matching entries? I tried wrapping it in NZ() but that didn't seem to fix it.

[CJ_London]

your code as written would fail - you are missing a double quotation mark

Try

BudgetEntry: (SELECT Sum(cost) FROM TFiscalData As TempBudget WHERE TransactionPrimary = "Budget" AND FCP = TempBudget.FCP)

[securitywyrm]

your code as written would fail - you are missing a double quotation mark

Try

BudgetEntry: (SELECT Sum(cost) FROM TFiscalData As TempBudget WHERE TransactionPrimary = "Budget" AND FCP = TempBudget.FCP)

Result is that it spits back the same number for every fund control point. Here's my entire SQL

SELECT TFiscalData.FCP, (SELECT Sum(cost) FROM TFiscalData As TempBudget WHERE TransactionPrimary = "Budget" AND FCP = TempBudget.FCP) AS BudgetEntry
FROM TFiscalData
GROUP BY TFiscalData.FCP;

At this point I'm trying to write a separate query for each field and then using relationships to link them together, but that gives me a new problem. A query for the budget numbers spits back 9 results out of 17, because only 9 of them had budgets. When put into a relationship, the null propogation means any FCP without a budget doesn't show up at all. So for this query...

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

How do I get it to spit back ALL the FCP, with a 0 for cost if there are no budget entries? I tried wrapping cost in NZ() but that didn't fix it.

[pbaldy]

Try a LEFT JOIN against the table that lists all the FCP.

[securitywyrm]

Took me awhile and some hair-pulling, but left join seems to be it! The part that got me was that left joins can only be singular, you can't have multiple left joins from the same point.

[pbaldy]

Glad you got it working.

[CJ_London]

my bad, should be

BudgetEntry: (SELECT Sum(cost) FROM TFiscalData As TempBudget WHERE TransactionPrimary = "Budget" AND FCP = TFiscalData.FCP)

[ssanfu]

<snip>
and have a text box siaplay the output of that query in a subform. This worked, BUT... this can only display one fund control point at a time, as subforms can't be in a continuous form.
<snip>

Why do you say this???

I just created a form, sub form and a sub-sub form, all in continuous forms view.
Access complains and changes one of the forms to a single form view, but you just change both main form and sub form (again) to continuous forms view. Save and open the form.......

[ArviLaanemets]

2 possible techniques to have continuous form with continuous subform:

1. (Have used it in several occasions.) Insert both continuous forms into unbound main form. Into unbound main form, add non-visible unbound text control(s). In form you want behave as parent form, in OnCurrent event set text control(s) in unbound form equal with index field(s) of datasource. Bind second form to unbound controls on unbound form;
2. (This one I haven't tried out.) Into header of your main form, add unbound invisible text boxes. In form's OnCurrent event, set unbound controls equal with index field(s) of datasource. Insert 2nd continuous form into footer of main form, and bind it to unbound controls in main form's header. Probably you can ditch unbound controls and link the subform directly to main forms controls (NB! To controls, not to fields!).