Combining the Results of Two Expressions

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
 
J

Jeff Boyce

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
 
G

Guest

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.
 
G

Guest

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;
 
G

Guest

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...
 
J

Jeff Boyce

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
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare results of two expressions within query 1
Combine Duplicates in Query 0
combining 2 expressions 3
Don't want columns with expressions to show 3
Query to display highest totals 1
Access Query problem 1
Adding Values From Two Numeric Fields 1
Ordering Columns in a Cross Tab Query & Dividing a Value 1

Top