Search Search for: Business ApplicationsAll Forums

Access Query: Count occurances of unique dates in a field and use it to get average

 mihaufo
Member with 41 posts.

Join Date: Mar 2012
Experience: Beginner
30-Jul-2012, 03:50 AM #1
Access Query: Count occurances of unique dates in a field and use it to get average
I have a table containing dates, product type, no. of units sold.

The date field contains duplicate dates.
ex:
DATE | PRODUCT | UNITS SOLD
1-JUN | A | 10
1-JUN | B | 23
2-JUN | A | 20
3-JUN | A | 30
4-JUN | A | 10
4-JUN | B | 30

Now, I would like to get the average of UNITS SOLD, based on number of unique dates.
How to calculate/count number of unique dates from the date field.
In the above example the number of unique dates should be 4 and average should be (123/4) = 30.75

Thanks

Last edited by mihaufo; 31-Jul-2012 at 01:58 AM..
 GeorgeJ
Member with 144 posts.

Join Date: Jan 2012
Location: Scotland
Experience: Intermediate
30-Jul-2012, 04:01 AM #2
Does this do it?

=SUM(C1:C7)/SUM(IF(FREQUENCY(A1:A7,A1:A7)>0,1))
 mihaufo
Member with 41 posts.

Join Date: Mar 2012
Experience: Beginner
30-Jul-2012, 05:43 AM #3
Yes, this works in Excel, but I would like to do it in Access.
 GeorgeJ
Member with 144 posts.

Join Date: Jan 2012
Location: Scotland
Experience: Intermediate
30-Jul-2012, 08:11 AM #4
Might be worth editting your first post and putting Access Query: in the title. I'm afraid i'm useless with Access. Sorry.
 techguy.org/1063127
As Seen On

WELCOME TO TECH SUPPORT GUY!

If you're not already familiar with forums, watch our Welcome Guide to get started.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)