1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by mihaufo, Jul 30, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. mihaufo

    mihaufo Thread Starter

    Joined:
    Mar 15, 2012
    Messages:
    41
    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
     
  2. GeorgeJ

    GeorgeJ

    Joined:
    Jan 16, 2012
    Messages:
    144
    Does this do it?

    =SUM(C1:C7)/SUM(IF(FREQUENCY(A1:A7,A1:A7)>0,1))
     
  3. mihaufo

    mihaufo Thread Starter

    Joined:
    Mar 15, 2012
    Messages:
    41
    Yes, this works in Excel, but I would like to do it in Access.
     
  4. GeorgeJ

    GeorgeJ

    Joined:
    Jan 16, 2012
    Messages:
    144
    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
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1063127