Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Average only if there is a value

Discussion in 'Information Technology' started by Cranky, Jul 28, 2006.

Tags:
  1. Cranky

    Cranky
    Expand Collapse
    Guest

    Hi

    I have a database of around 400 people who shown an interest in getting
    financial assistance. Of those, some have shown the interest, but not
    returned an application form, some have returned the form and are
    awaiting processing, and some have been processed. The information that
    defines these are stored in fields called 'DateFormRecd',
    'HBW£','HBA£','CTW£' and 'CTA£'

    How do I structure a query to give me an average of only those who've
    returned the form?

    Thanks in advance

    S:)
     
  2. Loading...

    Similar Threads Forum Date
    Sikhi About The Average Sikh Sikh Sikhi Sikhism Feb 18, 2016
    India Corruption rate in India is double of global average Breaking News Jul 9, 2013
    Average Jatt Sikh Male Height 5' 7" Health & Nutrition Aug 7, 2010
    Tech turns average Joes into mini-Spielbergs (USATODAY.com) Interfaith Dialogues Apr 1, 2005
    Sikh Values Blogs Oct 21, 2015

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Cranky wrote:
    > Hi
    >
    > I have a database of around 400 people who shown an interest in
    > getting financial assistance. Of those, some have shown the interest,
    > but not returned an application form, some have returned the form and
    > are awaiting processing, and some have been processed. The
    > information that defines these are stored in fields called
    > 'DateFormRecd', 'HBW£','HBA£','CTW£' and 'CTA£'
    >
    > How do I structure a query to give me an average of only those who've
    > returned the form?
    >
    > Thanks in advance
    >
    > S:)


    I am not clear from what you wrote what criteria or set of criteria
    would select only records with value. In any case, you need to select a
    criteria that will select only records with value then your average will
    only include those records.


    --
    Joseph Meehan

    Dia duit
     
  4. Cranky

    Cranky
    Expand Collapse
    Guest

    Joseph Meehan wrote:

    > I am not clear from what you wrote what criteria or set of criteria
    > would select only records with value. In any case, you need to select a
    > criteria that will select only records with value then your average will
    > only include those records.


    Hi Joseph, thanks for replying.

    It would be to average those only where the 'DateFormRecd' field is
    filled.

    S:)
     
  5. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    What do you want an average of. Can't really see how you can get an
    "average" of people who have sent froms back or any other items in your list.
    You could (for example) have a percentage of the total forms sent out, etc
    but an average is simply a sum the total number / a count of the items you
    wish to average and for that you need a number that you wih to divide the
    total by.

    Have you some more information or, better still a small sample of the data.


    --
    Wayne
    Manchester, England.



    "Cranky" wrote:

    > Hi
    >
    > I have a database of around 400 people who shown an interest in getting
    > financial assistance. Of those, some have shown the interest, but not
    > returned an application form, some have returned the form and are
    > awaiting processing, and some have been processed. The information that
    > defines these are stored in fields called 'DateFormRecd',
    > 'HBW£','HBA£','CTW£' and 'CTA£'
    >
    > How do I structure a query to give me an average of only those who've
    > returned the form?
    >
    > Thanks in advance
    >
    > S:)
    >
    >
     
  6. Cranky

    Cranky
    Expand Collapse
    Guest

    Sorry, I'm being dense.

    It's a benefit take-up campaign. A flier went out with Council Tax
    bills (UK) asking people to return the tear-off if they were
    interested. Those sent in had a full application form sent to them.

    Those who returned their form are either awaiting assessment, or have
    been assessed, for Housing Benefit, and/or Council Tax benefit. I have
    to be able to show the elected members how much benefit has been paid
    out as a result of the scheme. I can do the 'Sum of arrears paid' bit
    for HB and CT easily, but hten I grind to a halt.

    So, _of those who returned the application form (DateFormRec'd)_ I need
    to know the average of each of the following:

    Weekly Housing Benefit (HBW£)
    Housing Benefit Arrears (HBA£)
    Weekly Council Tax Benefit (CTW£)
    Council Tax Benefit Arrears (CTA£)

    There are around 400 people who showed an interest, of which 60 have
    returned the full forms. So far around 30 have been assessed. I want to
    be able to see these averages change as each new claim is assessed.
    These aren't set rate payments, you see; each claim could get very
    different amounts.

    Cheers

    S:)
     
  7. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    Hi Cranky

    There are many way to get the average you are after (where in the sql -
    =iif(ect etc). But it would be a good idea to try and keep things as simply
    as possible. So why not simply put in some criteria in your query to show
    only who has sent back thier forms - if you have a yes no box on your form
    you could set 1 as the criteria. Or you could use >1 for anyone who has
    recieved anything ( or <1 if you work in arrears).
    Once you have only the people you want simply press the totals option button
    at the top of the screen and see what you get (there is an avg option on the
    drop down)

    Dont forget you can always use the query to feed another query (with totals)

    Do I get a reduction in my council tax for this ?? (smile)
    --
    Wayne
    Manchester, England.



    "Cranky" wrote:

    >
    > Sorry, I'm being dense.
    >
    > It's a benefit take-up campaign. A flier went out with Council Tax
    > bills (UK) asking people to return the tear-off if they were
    > interested. Those sent in had a full application form sent to them.
    >
    > Those who returned their form are either awaiting assessment, or have
    > been assessed, for Housing Benefit, and/or Council Tax benefit. I have
    > to be able to show the elected members how much benefit has been paid
    > out as a result of the scheme. I can do the 'Sum of arrears paid' bit
    > for HB and CT easily, but hten I grind to a halt.
    >
    > So, _of those who returned the application form (DateFormRec'd)_ I need
    > to know the average of each of the following:
    >
    > Weekly Housing Benefit (HBW£)
    > Housing Benefit Arrears (HBA£)
    > Weekly Council Tax Benefit (CTW£)
    > Council Tax Benefit Arrears (CTA£)
    >
    > There are around 400 people who showed an interest, of which 60 have
    > returned the full forms. So far around 30 have been assessed. I want to
    > be able to see these averages change as each new claim is assessed.
    > These aren't set rate payments, you see; each claim could get very
    > different amounts.
    >
    > Cheers
    >
    > S:)
    >
    >
     
  8. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Cranky wrote:
    > Joseph Meehan wrote:
    >
    >> I am not clear from what you wrote what criteria or set of
    >> criteria would select only records with value. In any case, you
    >> need to select a criteria that will select only records with value
    >> then your average will only include those records.

    >
    > Hi Joseph, thanks for replying.
    >
    > It would be to average those only where the 'DateFormRecd' field is
    > filled.
    >
    > S:)


    Depending on the filed type that might be >0 or is not null etc.

    --
    Joseph Meehan

    Dia duit
     
  9. Cranky

    Cranky
    Expand Collapse
    Guest

    Wayne-I-M wrote:

    > So why not simply put in some criteria in your query to show
    > only who has sent back thier forms - if you have a yes no box on your form
    > you could set 1 as the criteria. Or you could use >1 for anyone who has
    > recieved anything ( or <1 if you work in arrears).
    > Once you have only the people you want simply press the totals option button
    > at the top of the screen and see what you get (there is an avg option on the
    > drop down)


    Great; thanks. I'll give that a go.
    *update* Yup, works perfectly. Thank you!

    > Do I get a reduction in my council tax for this ?? (smile)


    Hah! Nice try, wiseguy.
    No. You don't. :)

    Steve
     

Share This Page