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
"KARL DEWEY" wrote:
> 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.
>
> --
> Build a little, test a little.
>
>
> "WembleyBear" wrote:
>
> > 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
> > --
> > Access 2007, Windows XP
|
Similar Threads
