Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010

    Unanswered: using averages in a query - help please


    new here and first question so i hope someone can help.

    I have a set of client data giving start date and end date, complete with serivce used.

    from this i need to be able to break this dwon to show how many clients use the service each month.

    ie client 1 uses the service from 01/02 to 30/04
    client 2 uses service 01/03 to 31/05
    client 3 uses service 01/04 to 30/06

    therefore results returned should show

    1 client feb
    2 client mar
    3 clients Apr
    2 clients may
    1 client June (i think!)

    im just need to know number of clients at end of each month - but only have the start and end date for period.

    Any idea how to do this in a simple query?

    many thanks

  2. #2
    Join Date
    Oct 2009
    begin a query with your core client, startdate, enddate data;

    then you are going to add ultimately 12 calculated fields - one for each month...start with 1 or 2 until you get it working right and then expand.

    you will need to get to know the 'iif' method...look that up in Help, your text book, or google about - - as you are going to rely on this alot

    figure out the logic that will give you always the correct result

    so your calc field is going to look something like this:
    Jan: iif((StartDate>=1/1/10 AND StartDate<2/1/10),1,0)

    This will put a 1 into the field if valid or a 0 into the field if not valid.

    Then in your form or report or if necessary in another separate query that is based on this query - - you will sum these columns.

    p.s. the word 'Date' is reserved - you can not use that - - always use StartDate or some variation of the word whenever you write code or make calculated fields....

  3. #3
    Join Date
    Jun 2010

    thank you


    that makes sense - i have some knowledge of access but its been a while since i have used it, so i know how to use iif.

    thansk for the speedy response, and great service!

    i can get back to my work now

    thanks again

  4. #4
    Join Date
    Jun 2007
    Maitland NSW,Australia
    BEWARE a service could be used between December 2010 and January 2011. How will you differentiate Janauary 2011 from January 2010 when they both have a month value of 1?

  5. #5
    Join Date
    Jun 2007
    Maitland NSW,Australia
    Using the IF statements will only give the month of the Start Date but you also need to flag the months between the Start and End Dates as a service month.

    I have attach a sample database (2007 format) that uses code to populate an array to detemine the months of service (assuming the period of service is not more than 12 months). The array is then written to a table and a query is used to display the totals.

    Run the macro macro_year_totals.

    tbl_clients - table that has the raw data
    tbl_services - table that stores the calculated data from the array
    qry_year_services - a query that displays the total
    macro_year_totals - runs the code and opens the query qry_year_services
    mod_client_services - runs code to read each record in the raw data, flag each month between the start and finish dates as a month of service and store these values in an array. When all the records in the raw data are read the output is written to tbl_services.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts