Welcome to SPN

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

Sign Up Now!

where in selection query?

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

  1. sybmathics

    sybmathics
    Expand Collapse
    Guest

    I am really puzzled about the "where" option in a totals query.

    To get data for a specified year i'd normally use Myyear:year([datefield])
    and add a criterium for the needed year.

    I've seen another user use the "where" statement replacing the "group by"
    row.
    Where can I find information about this "where"-thing.


    Sybolt

    --
    ||//////||
    ( o o )
    ( O )
    -
    ( )
    ( )
    (______O______)
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    A Total query has a GROUP BY clause, which defines how the data is
    aggregated. It may or may not have a WHERE clause as well.

    If it has a WHERE clause, Access applies that before selecting the records
    and grouping them. For example, if you only want the records for a date
    period, it would be most efficient to use a WHERE clause so JET does not
    have to add them all up and then discard the other periods.

    If the Total query has a HAVING clause, this is applied after aggregation.
    It makes sense to use this where you want to apply criteria on the
    aggregarted total. For example if you want the query to return only those
    clients that had 10 or more invoices in the period, you would use the WHERE
    clause to exclude invoices outside that period, and then the HAVING clause
    on the count of invoices (which is only available after JET has aggregated
    them.)

    In query design view, you are applying a HAVING clause when you put criteria
    under a field that has GROUP BY in the Total row. If you want to use the
    WHERE clause, you must choose WHERE in the Total row of the query.
    (Sometimes that means the field actually turns up twice in the query design
    grid: once with WHERE so you can exclude records, and again with GROUP BY or
    some other form of aggregation.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "sybmathics" <s.hoitinga@hccnet.nl> wrote in message
    news:4466e5dd$0$15539$e4fe514c@dreader14.news.xs4all.nl...
    >I am really puzzled about the "where" option in a totals query.
    >
    > To get data for a specified year i'd normally use Myyear:year([datefield])
    > and add a criterium for the needed year.
    >
    > I've seen another user use the "where" statement replacing the "group by"
    > row.
    > Where can I find information about this "where"-thing.
     
  4. sybmathics

    sybmathics
    Expand Collapse
    Guest

    Thanks very much.
     

Share This Page