Query Assistance: Actual and Budget

[jstevens]

I'm trying to develop a query for pulling actual and budget information by month in a column format. I can pull MTD for each period for either actual or budget data: Per01 --> Per12

What I would like to do is pull the actual months and remaining budget months based on a variable.

Example: oVariable = 4 (representing 4 months of actuals)
Per01, Per02, Per03, Per04 would be actuals
Per05 to Per12 would be budget values

[HansV]

How do you want to specify the variable? In a text box on a form, or ...?

[jstevens]

Hans,

In a text box on a form.

[HansV]

What do the source data look like?

And what shape should the query output have? Can you provide an example?

[jstevens]

Hans,

Here is a sample of the query output. Grouped by Account and SubAccount.

EL_72.png

[HansV]

Thanks. And how about the source data?

[jstevens]

Hans,

Source data looks like this.

EL_73.png

[HansV]

How can I see what is Actual and what is Budget?

[jstevens]

Hans,

The "Source" dimension will either be Actuals or Budget.

[HansV]

We'll do this in three steps. Let's say the threshold is entered in the text box txtMonth on the form frmInput.

1) On the Create tab of the ribbon, click Query Design.
Add dbo.Data, then close the Add Tables dialog.
Add the Account, SubAccount, StmtDate, Amount and Source fields to the query grid.
In the Criteria row of the Source column, enter

IIf(Month([StmtDate])<=[Forms]![frmInput]![txtMonth],"Actuals","Budget")

Clear the Show check box of the Source column.

Save this query as - say - qryData.

2) Create a new table named tblMonths, with a single field of type date named MonthEnd. This field is also the primary key.
Populate the table with the last day of each month:

01/31/2020
02/29/2020
03/31/2020
...
12/31/2020

3) On the Create tab of the ribbon, click Query Design.
Add tblMonths and qryData, then close the Add Tables dialog.
Add Account and SubAccount from qryData to the query grid.
In the next available column, create a calculated column:

Period: "Per" & Format([MonthEnd],"mm")

Add the Amount and StmtDate fields from qryData to the query grid.
In the Criteria row of the StmtDate column, enter

<=[MonthEnd]

In the Query Type group of the Design tab of the ribbon, click to highlight Crosstab.

The Total options for the columns are:
Account: Group By
SubAccount: Group By
Period: Group By
Amount: Sum
StmtDate: Where

The Crosstab options for the columns are:
Account: Row Heading
SubAccount: Row Heading
Period: Column Heading
Amount: Value
StmtDate: none (leave blank)