Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

mihaufo's Avatar
mihaufo mihaufo is offline
Computer Specs
Member with 41 posts.
THREAD STARTER
 
Join Date: Mar 2012
Experience: Beginner
30-Jul-2012, 04: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 02:58 AM..
GeorgeJ's Avatar
GeorgeJ GeorgeJ is offline
Member with 144 posts.
 
Join Date: Jan 2012
Location: Scotland
Experience: Intermediate
30-Jul-2012, 05:01 AM #2
Does this do it?

=SUM(C1:C7)/SUM(IF(FREQUENCY(A1:A7,A1:A7)>0,1))
mihaufo's Avatar
mihaufo mihaufo is offline
Computer Specs
Member with 41 posts.
THREAD STARTER
 
Join Date: Mar 2012
Experience: Beginner
30-Jul-2012, 06:43 AM #3
Yes, this works in Excel, but I would like to do it in Access.
GeorgeJ's Avatar
GeorgeJ GeorgeJ is offline
Member with 144 posts.
 
Join Date: Jan 2012
Location: Scotland
Experience: Intermediate
30-Jul-2012, 09: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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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


(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑