Combining the Results of Two Expressions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have twelve columns. In each column, I use an expression to calculate
revenue for a month. Is there a way to add another expression that sums
certain columns? Basically, I want to add a subtotal every three months for
"quarter" total and then sum all twelve of the columns for a total for the
year.

Can I do this in the same query or would it be better to create a new query
that calls the first one? Or maybe even have the first query create a
temporary table and build the final query off that table (does that make
sense)?

Thanks!
-Nick
 
Nick

I may be reading too much into your description, but ...

It sounds like you are using a spreadsheet, not a relational database.
Twelve columns related to months sounds like a "January" column, a
"February" column, ... While this might be the standard approach when you
work in Excel, you will not get the best use of Access'
relationally-oriented features and functions if you feed it 'sheet data.

In Access, look to design your tables "narrow & deep" (thanks to John V. for
this concept), rather than "wide & shallow" (a spreadsheetly way to do it).
Summing in Access involves reading "down", not "across".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
This is in a query, not a table. All of the data is stored "narrow and deep"
in tables. The idea here is to bring it "wide and shallow" for the user to
see and export into Excel. Technically, they could perform the summations
themselves after the data is there, but I would rather have the summations
export as well.
 
Everything calculates just fine, but I would like to take "Q1 Sep Sales," "Q1
Oct Sales," and "Q1 Nov Sales," and combine their results into a new column
called "Q1 Sales." Is that possible to do in the same query?

SELECT tblWeeklyPipeLine.SAPOMID,
[TotalValue]*([WinRate]/100) AS WeightedValue,
[TotalValue]*[CIRate]/100 AS CIValue,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsStartDate],[tblWeeklyPipeline].[OSStartDate]) AS plStartDate,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsEndDate],[tblWeeklyPipeline].[OSEndDate]) AS plEndDate,
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],1,[WeightedValue]) AS [Q1
Sep Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],2,[WeightedValue]) AS [Q1
Oct Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],3,[WeightedValue]) AS [Q1
Nov Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],4,[WeightedValue]) AS [Q2
Dec Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],5,[WeightedValue]) AS [Q2
Jan Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],6,[WeightedValue]) AS [Q2
Feb Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],7,[WeightedValue]) AS [Q3
Mar Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],8,[WeightedValue]) AS [Q3
Apr Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],9,[WeightedValue]) AS [Q3
May Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],10,[WeightedValue]) AS [Q4
June Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],11,[WeightedValue]) AS [Q4
July Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],12,[WeightedValue]) AS [Q4
Aug Sales]
FROM tblOpportunity INNER JOIN tblWeeklyPipeLine ON tblOpportunity.SAPOMID =
tblWeeklyPipeLine.SAPOMID;
 
Do it like this ---
SELECT tblWeeklyPipeLine.SAPOMID,
[TotalValue]*([WinRate]/100) AS WeightedValue,
[TotalValue]*[CIRate]/100 AS CIValue,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsStartDate],[tblWeeklyPipeline].[OSStartDate]) AS plStartDate,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsEndDate],[tblWeeklyPipeline].[OSEndDate]) AS plEndDate,
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],1,[WeightedValue]) AS [Q1
Sep Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],2,[WeightedValue]) AS [Q1
Oct Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],3,[WeightedValue]) AS [Q1
Nov Sales],
(CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],1,[WeightedValue]) +
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],2,[WeightedValue]) +
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],3,[WeightedValue])) AS [Q1
Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],4,[WeightedValue]) AS [Q2
Dec Sales],


--
KARL DEWEY
Build a little - Test a little


Nick said:
Everything calculates just fine, but I would like to take "Q1 Sep Sales," "Q1
Oct Sales," and "Q1 Nov Sales," and combine their results into a new column
called "Q1 Sales." Is that possible to do in the same query?

SELECT tblWeeklyPipeLine.SAPOMID,
[TotalValue]*([WinRate]/100) AS WeightedValue,
[TotalValue]*[CIRate]/100 AS CIValue,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsStartDate],[tblWeeklyPipeline].[OSStartDate]) AS plStartDate,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsEndDate],[tblWeeklyPipeline].[OSEndDate]) AS plEndDate,
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],1,[WeightedValue]) AS [Q1
Sep Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],2,[WeightedValue]) AS [Q1
Oct Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],3,[WeightedValue]) AS [Q1
Nov Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],4,[WeightedValue]) AS [Q2
Dec Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],5,[WeightedValue]) AS [Q2
Jan Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],6,[WeightedValue]) AS [Q2
Feb Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],7,[WeightedValue]) AS [Q3
Mar Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],8,[WeightedValue]) AS [Q3
Apr Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],9,[WeightedValue]) AS [Q3
May Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],10,[WeightedValue]) AS [Q4
June Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],11,[WeightedValue]) AS [Q4
July Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],12,[WeightedValue]) AS [Q4
Aug Sales]
FROM tblOpportunity INNER JOIN tblWeeklyPipeLine ON tblOpportunity.SAPOMID =
tblWeeklyPipeLine.SAPOMID;

KARL DEWEY said:
Post your SQL for a look see.
Click to expand...
Click to expand...
 
Nick

Consider posting your solution. Other folks looking through the newsgroup
for help might find your question and wonder how to solve it too.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Nick,

What does your CalculateSales function do?

It would seem that there is probably a better (faster) way to do this. Can
you post the structure of your "Narrow and Deep" table, and the code for the
CalculateSales function. If so, you might be able to speed up this process.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


Nick said:
Everything calculates just fine, but I would like to take "Q1 Sep Sales," "Q1
Oct Sales," and "Q1 Nov Sales," and combine their results into a new column
called "Q1 Sales." Is that possible to do in the same query?

SELECT tblWeeklyPipeLine.SAPOMID,
[TotalValue]*([WinRate]/100) AS WeightedValue,
[TotalValue]*[CIRate]/100 AS CIValue,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsStartDate],[tblWeeklyPipeline].[OSStartDate]) AS plStartDate,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsEndDate],[tblWeeklyPipeline].[OSEndDate]) AS plEndDate,
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],1,[WeightedValue]) AS [Q1
Sep Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],2,[WeightedValue]) AS [Q1
Oct Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],3,[WeightedValue]) AS [Q1
Nov Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],4,[WeightedValue]) AS [Q2
Dec Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],5,[WeightedValue]) AS [Q2
Jan Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],6,[WeightedValue]) AS [Q2
Feb Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],7,[WeightedValue]) AS [Q3
Mar Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],8,[WeightedValue]) AS [Q3
Apr Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],9,[WeightedValue]) AS [Q3
May Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],10,[WeightedValue]) AS [Q4
June Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],11,[WeightedValue]) AS [Q4
July Sales],
CalculateSales([SignDate],[GroupDeal],[RFY],[RFM],12,[WeightedValue]) AS [Q4
Aug Sales]
FROM tblOpportunity INNER JOIN tblWeeklyPipeLine ON tblOpportunity.SAPOMID =
tblWeeklyPipeLine.SAPOMID;

KARL DEWEY said:
Post your SQL for a look see.
Click to expand...
Click to expand...
 
Back
Top