Calculations in Queries

[accessewbie]

Okay I think I have gone backwards. I have a table with the following fields:

TABLE
Customer
Region - (drop down with canada, north central, west, east, etc.)
Status - (drop down with win, loss, pending decision, etc).
Writer
Comments
(Other informational fields)

REPORT!
What I did was create a report that sorts by region and then count all the wins, losses, and pending decisions for each region. Then I calculated the perecentages for each region. So for example say this is the data for Canada:
Canada
10 Wins
5 Losses
2 Pending Decisions
Then the percentages read 58.82% wins, 29.4% losses, 11.76%

Then it would go on and do the same for the West, East, and other Regions

Here are my formulas in the REPORT:

Status Footer
="Total Customers with a status of" & " " & [Status] & " : " & Count(*) & " " & IIf(Count(*)=1,"")

Region Footer
Percent Wins: =(Sum(Abs([Status]="Win")))/Count(*)
Percent Losses: =(Sum(Abs([Status]="loss")))/Count(*)
Percent Pending Decision: =(Sum(Abs([Status]="pending decision")))/Count(*)

However, I discovered I can't build a graph off the results from the formulas in my report but instead I have to do this through a query. So that is my problem. I have no idea where to begin to make this work through in a query. Because the formulas from the report don't work in the query. Please help. thanks in advance! I hope what I am asking is clearly defined.

 

[lespaul]

TABLE
Customer
Region - [b](drop down with canada, north central, west, east, etc.)[/b]
Status - [b](drop down with win, loss, pending decision, etc).[/b]
Writer
Comments
(Other informational fields)

First, the information in bold above, does this mean that you have used Look-up Fields IN THE TABLE? If so, you should read The Evils of Lookup Fields in Tables.

Second, can you post the SQL query that you are using for the report so that someone can help you modify it so the information you are looking for is IN the query rather than in the Report?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[accessewbie]

Okay I looked in Design view of the table (Data Type says Text) and checked the lookup tab and I do have the Display Control as Combo Box, Row Source Type as Value List, and then the list of regions on Row Source (for Region & Status). Is this not a good idea? I have these b/c the team often times use abbreviations or will mispell causing reports not to pull the right information. I will read the link. Thanks again for all your help!!!

Okay the SQL query is pasted below. As you can tell I have no formulas (since they were all in my report).

SELECT [Proposals Database].Customer, [Proposals Database].Region, [Proposals Database].[Account Manager], [Proposals Database].[Proposal Specialist], [Proposals Database].[Date Received], [Proposals Database].[Date Due], [Proposals Database].[Date Shipped], [Proposals Database].[Yearly Value], [Proposals Database].Category, [Proposals Database].Incumbent, [Proposals Database].Status, [Proposals Database].[Short-listed], [Proposals Database].Comments
FROM [Proposals Database]
ORDER BY [Proposals Database].Region;

 

[accessewbie]

Could someone possibly help me modify the formulas I had in the report to work in the query I posted above? Your help is greatly apperciated. Thanks!

 

[lespaul]

can you provide some sample data from the [Proposals Database] and what you would expect as the results from that data?

 

[accessewbie]

Okay I made up some data and only used the fields that I would be concerned with.

Customer Region Category Status
Cust 1 Canada Mid-Market Loss
Cust 2 EMEA Corporate Pending

The report I built looks something like this (based on the data) using this formula ="Total Customers with a status of" & " " & [Status] & " : " & Count(*) & " " & IIf(Count(*)=1,""):

Report Results:
Region: Canada
Total Customers with a status of Win : 1
Total Customers Pending Decision : 2
Total Customers with a status of No Decision : 3
Total Customers with a status of Loss : 4
Total Customers with a status of In Progress : 5
Total Customers for Canada : 15

Region: EMEA
Total Customers Pending Decision : 1
Total Customers with a status of Loss : 2
Total Customers for EMEA : 3

But I need these formulas in a query so I can build a graph. Does this help or make it more confusing? Thanks!

 

[lespaul]

So with those two records above (Cust 1 and Cust 2) you would expect:

Customer    Win    Pending    No Decision   Loss    In Progress   Total
Canada       1       2            3          4        5            15
EMEA         1       0            0          2        0            3

How do you come up with those numbers?

 

[accessewbie]

Yes,
There are several hundred records. And it counts every customer in Canada that shows a win status, a loss status, a pending decision status, etc. Then it does the same for EMEA, North West, or any other region there may be. Then it totals all for each region.

 

[PHV]

Something like this ?
SELECT Region, Sum(IIf(Status='Win',1,0)) As Win, Sum(IIf(Status='Loss',1,0)) As Loss, Sum(IIf(Status='Pending',1,0)) As Pending
, ..., Count(*) As Total
FROM [Proposals Database]
GROUP BY Region;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[accessewbie]

Thanks that works perfectly. So I went in and added my graph and used the option to preview graph. I couldn't see my data but only sample data in the report. So I used the Help on office and it says to use the Print Preview function (or open and close the report) to view the actual data results. I did both and saw my data when running the report. But is there any way to see the actual data in design view or edit the chart? I ask because I don't like how the legend appears and I want to edit the wording. Any suggestions? Thanks again and you have been a huge help.