A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculated field question



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 03:15 PM posted to microsoft.public.access.queries
WembleyBear
external usenet poster
 
Posts: 31
Default Calculated field question

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
  #2  
Old June 2nd, 2010, 05:34 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Calculated field question

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

  #3  
Old June 3rd, 2010, 11:53 AM posted to microsoft.public.access.queries
WembleyBear
external usenet poster
 
Posts: 31
Default Calculated field question

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.