Calculated field question

  • Thread starter Thread starter WembleyBear
  • Start date Start date
W

WembleyBear

I have a query that summarises sales data for a particular month and cost
centre. The data comes from our accounting system and is held in a single
table in my database thus:

tblNominal
------------
CostCode - Long Integer
ExpenseCode - Long Integer
Desc - Text
Current - Double
YTD - Doube
Month - Text
Year - Text

The query sums the Current & YTD values for a particular month, grouping
them by the Description field. This works fine to sum the Sales for say
Retail (which is a group of Expense Codes) but I also want the query to show
the Margin for that type of sale in a neighbouring column. This value is not
held in the table, but could be calculated as there is a group of codes
having the description Retail Cost of Sales, and Retail Margin would be
Retail - Retail Cost of Sales. Is this possible? What would be the best way
of going about this?

Thanks
Martyn
 
Post the SQL of the query you now have by opening in design view, click on
VIEW - SQL View, highlight all, copy, and paste in a post.
Include the field name where the 'group of codes having the description
Retail Cost of Sales' are stored (maybe ExpenseCode). Provide a list of the
codes for Cost of Sales.
 
SQL of the query is:

SELECT Nominal.CostCentre, Nominal.Month, Nominal.Year, Nominal.Desc,
Sum(Nominal.Current) AS SumOfCurrent, Sum(Nominal.YTD) AS SumOfYTD
FROM Nominal
GROUP BY Nominal.CostCentre, Nominal.Month, Nominal.Year, Nominal.Desc
HAVING (((Nominal.CostCentre)=1135) AND ((Nominal.Month)="April") AND
((Nominal.Year)="2010"));

The field holding the expense codes is as you correctly state called
ExpenseCode. The expense codes themselves all have themselves all have the
description Retail Sales within the CostCentre selected; they are codes
4000,4001,4030,4031,4090 & 4091. The cost of sale codes for Retail are
4003,4033 & 4093 - these all have the description Retail COS. My simple query
does a fine job of summarizing the totals for all the groups just by using
the description. The problem is that Retail COS is summarized there too, when
really I need calculated fields to minus this amount off of the Current & YTD
totals for that group in order to get the margin. And of course, Retail is
only an example - there are other groups in the download for Warranty,
Warranty COS etc

Ideally, I would like to a result something like this:

Description Current CurrentMargin YTD YTDMargin
------------ --------- ---------------- ----- ------------
Retail
Internal
Warranty
Contract
etc.


Martyn
 
Back
Top