Calculated Value on Form Query (1 Viewer)

[cowenpa]

Hi there - I understand the basics of doing calculated values on a form but a bit stuck on this more complex case

This is an example of the underlying query

Which generates

I then have this on an underlying subform

I want to have a field on here which is Profit which is Sales minus Cost of Sales

On my main form I am setting the source SQL to this form via VBA e.g. where the month is February and CAB is chosen..
Case 0
Me.FrmMonthDeptQuery.Form.SumOfFeb.ColumnHidden = False
FrmMonthDeptQuery.Form.RecordSource = "SELECT Forecast.Income_statement_section, Forecast.Dep, Sum(Forecast.Feb) AS SumOfFeb FROM Forecast GROUP BY Forecast.Income_statement_section, Forecast.Dep HAVING (((Forecast.Dep)='CAB'));"


How should I add a field to the subform and calculate it during the VBA I assume e.g. in this case profit would be sales of 296,681.63 - COS of 254,890.00 = 36,791.63

Thanks

Paul

 

[arnelgp]

use expression as ControlSource of an Unbound Textbox:

=DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '1. Sales'") -
DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '2. COS'")

 

[Minty]

I would display these results in a cross tab query, and display the query on the form as a sub form.

 

[cowenpa]

use expression as ControlSource of an Unbound Textbox:

=DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '1. Sales'") -
DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '2. COS'")

Thanks - that works when I put it on the form itself but when I try and change the value with code using
TxtProfit.ControlSource = DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '1. Sales'") + DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '2. COS'") Then I get #Name?

I have tried adding " as in
TxtProfit.ControlSource = "DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '1. Sales'") + DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '2. COS'")"


Both bits of code run ok and I haev also tried a requery TxtProfit.Requery but still shows #Name?- Any idea how to resolve/fix it?

I'm sure my code is very inefficient currently.

Thanks

Paul

 

[arnelgp]

TxtProfit.ControlSource = "=" & DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '1. Sales'") + DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '2. COS'")

or directly:

TxtProfit = DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '1. Sales'") + DLookup("SumOfFeb", "MonthDeptQuery2", "Dep = 'CAB' And Income_Statement_Section = '2. COS'")

 

[plog]

Is Forecast a table and does it have a field for every month (e.g. [Jan], [Feb], [Mar] etc)? If so you need to put aside this issue an properly structure your tables.

Just like you don't have a field for each department, you shouldn't have a field for each month. Instead you would have some sort of field to hold what date each value is for.

You need to fix your table issue before moving on to forms

 

[cowenpa]

Is Forecast a table and does it have a field for every month (e.g. [Jan], [Feb], [Mar] etc)? If so you need to put aside this issue an properly structure your tables.

Just like you don't have a field for each department, you shouldn't have a field for each month. Instead you would have some sort of field to hold what date each value is for.

You need to fix your table issue before moving on to forms

The data is pulled from Excel spreadsheets and yes it has figures for each month - what's the problem with that?

The forecast does have a column for each month

Conversely the actuals table does have columns for departments - this is all for one month (January)

 

[plog]

So how come Access is part of this? Why not just use Pivot Tables to get your reports?

Create a PivotTable to analyze worksheet data - Microsoft Support

How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

support.microsoft.com

 

[Pat Hartman]

Can you post the database? Obfuscate any sensitive data. I don't understand why you are specifically summing February. You might be able to create a crosstab query in Access and that will make the "Actuals" have the same format as the "forecast" so you can get the data on the same form. i.e. Actuals vs Forecast for each month.

 

[cowenpa]

So how come Access is part of this? Why not just use Pivot Tables to get your reports?

Create a PivotTable to analyze worksheet data - Microsoft Support

How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

support.microsoft.com

They were using pivottables already but wanted a better way to display the data to keep the MD happy. I think the database is coming along fairly well - there will be one report and then will detect whether that month is actual or forecast and then display an overview which you can drill down into - there will be 3 subforms drilling down further e.g. salaries has multiple categories then you click one of those and see any records for that.

 

[cowenpa]

Can you post the database? Obfuscate any sensitive data. I don't understand why you are specifically summing February. You might be able to create a crosstab query in Access and that will make the "Actuals" have the same format as the "forecast" so you can get the data on the same form. i.e. Actuals vs Forecast for each month.

A month is either actuals or forecast so for example January through April will now be actuals and May through December is forecasted. When May finished then the actuals get imported in to the database.

You would see the total for each income statement for a particular month and department - months and departments are both tabs

All the data above is pulled through VBA into the subform as you click on any of the tabs. Totals will be the total for all departments e.g. Sales from CAB, CON, DES, SALES, TCC, and VEA and same for COS..

If you click on May TCC you see the totals for May for that particular department.

If you click sales you get a breakdown of what that consists of

There would usually be more Income statement sections like this:

This is taken from the actuals form I was working on. I don't have a complete subset of data to play with - finance are going to provide me more to play around with.

Paul

 

[Pat Hartman]

You didn't post the database or the schema so we can only guess. One thing I did notice is that you don't seem to have Year in the table or in your domain function. If you just sum "Feb", that will sum "Feb" for all years. The Forecast table should look like:

ForecastID (autonumber PK)
ForecastYear (unique index fld 1 of 3)
ForecastMonth (unique index fld 2 of 3)
Category (unique index fld 3 of 3)
Amt

The Actual table probably has more columns if it represents transaction level detail but it should be summarized to the same format as the forecast.

Then you can flatten both tables using two CrossTab queries The results of the crosstab will be:

Year, Category with 12 columns for Amt. One for each month and a summary for the year.

If you join the forecast crosstab to the actuals crosstab, you can produce a report with two lines for forecast and actual and a third line for variance.


I'm no subject matter expert but I would have trouble with the interface you've created. It seems to require too much clicking on tabs. You never see a consolidated view of the data or actuals vs forecast which I think is probably the whole point of bothering to forecast.

 

[cowenpa]

Thanks for the comments but I'm being guided by what Finance says they want which is mainly with a view to presenting data to the MD in a way he is happy with. Yes, it is frustrating that the data is in totally different formats and would make it easier if it was consistent - I guess it's historical why it has been done that way. They haven't mentioned a comparison between forecast and actuals but yes I expect it would be useful but it may be that they provide that data directly from Excel or something.

 

[Pat Hartman]

Let me take a different tact. Do NOT use dLookup()s to populate the form. You are making way more work for yourself than you need to. Use queries If you want to show all the months on the form as your first picture shows, create a crosstab and bind the form to the crosstab. You don't have to display the data as rows, although that is most common. You would also use filters on the form to change the criteria for how to pull just what you want. Do NOT forget to include Year as part of your criteria and as part of your table. Otherwise, come fall when they create next year's forecast, you'll have multiple years in the database and that won't work without year in each row. Don't even think about creating a database for each year. That is a spreadsheet approach and will not give you an easy way to create year over year reports.