Query multiplication

  • Thread starter Thread starter Neil G
  • Start date Start date
N

Neil G

In my query, I have a column that does a calculation based on two other
columns thus

Average: ([Sum Of Points]/[SumOfrides])*5

My problems:
1) When I run the query, if a person took no rides, he therefore gets no
points, so the Average column gives a #Error - how might I overcome this
2) I want to sort descending (so that highest average is topmost) - this
isn't happening - is this because 1) above has errors?
3) Likewise, I'd like to display to 2dp, round figures are showing just '5'
or whatever, and others show 10dp - my field properties is showing 2dp -
again is this a conseuqence of part 1)

Thanks for your help

Neil
 
Hi Neil,

Have you considered using the IIF Function?

Example:
Average: IIF([SumOfrides]=0,0,([Sum Of Points]/[SumOfrides])*5)


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights



--------------------
| From: "Neil G" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Query multiplication
| Date: Tue, 8 Jun 2004 00:05:37 +0000 (UTC)
| Organization: BT Openworld
| Lines: 19
| Message-ID: <[email protected]>
| NNTP-Posting-Host: host81-155-242-203.range81-155.btcentralplus.com
| X-Trace: hercules.btinternet.com 1086653137 28489 81.155.242.203 (8 Jun
2004 00:05:37 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Tue, 8 Jun 2004 00:05:37 +0000 (UTC)
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MSMail-Priority: Normal
| X-Priority: 3
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!diablo.theplanet.net!zen.net.uk!demorgan.zen.co.u
k!194.72.9.35.MISMATCH!news-peer1!btnet-feed5!btnet!news.btopenworld.com!not
-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:203101
| X-Tomcat-NG: microsoft.public.access.queries
|
| In my query, I have a column that does a calculation based on two other
| columns thus
|
| Average: ([Sum Of Points]/[SumOfrides])*5
|
| My problems:
| 1) When I run the query, if a person took no rides, he therefore gets no
| points, so the Average column gives a #Error - how might I overcome this
| 2) I want to sort descending (so that highest average is topmost) - this
| isn't happening - is this because 1) above has errors?
| 3) Likewise, I'd like to display to 2dp, round figures are showing just
'5'
| or whatever, and others show 10dp - my field properties is showing 2dp -
| again is this a conseuqence of part 1)
|
| Thanks for your help
|
| Neil
|
|
|
 
Back
Top