Expression in Query

[durdle]

Hey,


I have a query named "qrymondayavgcalls" which includes just two fields, "Date" and "CallsOffered". So when you run this query you will get these 5 numbers in the calls offered field
578
209
287
411
420
Total
1905
So What I want to include is another expression to Sum those 5 fields and divide it by each field.
call off Percent
578 30.3%
209 11.0%
287 15.1%
411 21.6%
420 22.0%


See Attached.
Please Help.

Thanks
________
silversurfer reviews

 

[Mile-O]

I don't see how you would expect to do this within this query as you are trying to calculate in two different directions: horizontally along fields, and then vertically through rows.

The rows are the results of a query, the fields are what you use to produce the results. You are proposing to calculate a field based on the result of the query which has yet to calculate itself.

I played with your query for a bit but was unable to get a result.

I changed one thing though.

Your method of selecting every Monday within a month seemed too longwinded. By adding two calculated fields you can now edit the query to deliver any day of the week you want in any month.

I've still set it to every Monday in March just now.

Where:

Sunday = 1
Monday = 2
...
Saturday = 6



And:

January = 1
...
December = 12

 

[durdle]

Thank you for your time Mile-O-Phile.
Do anyone else have any sugguestions on how I can obtain my result?

Any help would be great.

Thanks
Chris
________
colorado dispensaries

 

[Nouba]

Change Mile-O-Phile's query to something like

SELECT
  fdate
, (Sum(acd_calls) + Sum(abn_calls)) / 
    (SELECT
      Sum(acd_calls) + Sum(abn_calls)
    FROM tblstats
    WHERE
      Weekday(fdate)=2 AND
      Month(fdate)=3) AS PercentageOfCalls
FROM tblstats
WHERE
  Weekday(fdate)=2 AND
  Month(fdate)=3
GROUP BY fdate

 

[raskew]

Norbert-

An elegant solution!!

To improve readability Durdle could carry your solution one step further by (in query design mode) highlighting PercentageOfCalls, right click to bring up Properties, then selecting Percent as the Format.