Its easy to calculate averages in Access queries using the Totals method, but what do you do in the following instance?
month-------quantity
1 ------------10
2-------------0
3-------------20
4-------------0
5-------------20
The normal way would be to add up and divide by the months
ie 50/5 = 10
My problem is that i want to EXCLUDE the months where you have a ZERO quantity.
then 50/3 = 16.67
Any suggestions on how to calculate this in a Query?
- Debug.Print FormatNumber((DSum("[Quantity]", "Table1", "[Quantity]>0")) / (DCount("[Month]", "Table1", "[Quantity]>0")), 2)
Result:16.67 Or: - SELECT FormatNumber((DSum("[Quantity]","Table1","[Quantity]>0"))/(DCount("[Month]","Table1","[Quantity]>0")),2) AS Result
-
FROM Table1
-
GROUP BY FormatNumber((DSum("[Quantity]","Table1","[Quantity]>0"))/(DCount("[Month]","Table1","[Quantity]>0")),2);
Result: 5 4310 - Debug.Print FormatNumber((DSum("[Quantity]", "Table1", "[Quantity]>0")) / (DCount("[Month]", "Table1", "[Quantity]>0")), 2)
Result:16.67 Or: - SELECT FormatNumber((DSum("[Quantity]","Table1","[Quantity]>0"))/(DCount("[Month]","Table1","[Quantity]>0")),2) AS Result
-
FROM Table1
-
GROUP BY FormatNumber((DSum("[Quantity]","Table1","[Quantity]>0"))/(DCount("[Month]","Table1","[Quantity]>0")),2);
Result:
thx i will give it a try today
Mr Adezii, have you perhaps still got the file you used with the above formula? I would like to see how the code was used in the query. thx
I just recreated the Query for you - here it goes.
thx i will try again to use it
Sign in to post your reply or Sign up for a free account.
Similar topics
by: francophone77 |
last post by:
What is the best way to setup a query to select records in specific
months only. For instance if I want to compare sales in the month of
May regardless of year.
TIA
|
by: HEMH6 |
last post by:
Weihted Average
Write a C program to calculate and print the weighted average of a list of N
floating point number, using the formula
Xave = F1X1 + F2X2+...+ FnXn
where the F's are...
|
by: abouddan |
last post by:
Hi all
I am working on an accounting project using MS Access 2000, that demands to calculate many fields in a spesific record.
The problem:
The query I am using returns many records and for each...
|
by: John Grewar |
last post by:
Hello,
I have actually asked this before but can't find it on the site (I'm still new here). I have got numerical data coming into my database twice a day for each specific subject that is being...
|
by: gaga |
last post by:
hi guys,
a part of my program requires me to calculate an average of items that
are sold. the easiest way to do that would be writing a function, but
im having trouble making up the parameters. if...
|
by: paeh |
last post by:
hello..can anyone help me. I am beginner in programming. I need to make a system that can calculate moving average.
my system process will be executed according to certain schedule such as daily,...
|
by: Gosi75 |
last post by:
I have to columns, one called CloseProbability and the other Stepname. The CloseProbability includes some steps in percents(0,10,20,40,60,80 and 100%) and each CloseProbability is attached to certain...
|
by: hanie |
last post by:
a student wants to know his grade average for the semester. the grades are give in letter grades with numeric equivalents. develop a solution to calculate a grade average given the letter grades(the...
|
by: Wiredboy |
last post by:
Hi, i'm pretty new in this and i hope somebody can help me with this:
i have a db with 5 columns with different values and i want to calculate the average of them. I've tried to create a formula...
|
by: Ilina Ivanova |
last post by:
Please, help me!
I have a DB (Access 2007) with 4 columns. The first is the ID of the patient and the others are indexes PB1 , PB2, PB3. I have difficulties to calculate the average of columns PB1,...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |