Welcome to SPN

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

Sign Up Now!
  1. Monthly (Recurring) Target: $300 :: Achieved: $95
      Become a Supporter    ::   Make a Contribution   

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.
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page