Prev: deduct order quantity from credit limit
Next: Last record/entry from a table
From: WembleyBear on 2 Jun 2010 10:15
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
From: KARL DEWEY on 2 Jun 2010 12:34
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
From: WembleyBear on 3 Jun 2010 06:53
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
 | 
Pages: 1
Prev: deduct order quantity from credit limit
Next: Last record/entry from a table