Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query field data type (Access 2002)

    Is it possible to only query certain field data types from a query.
    ie:
    SELECT SUM(<all numeric fields>)
    FROM TableWithManyNumericFieldsButNotAllNumberic;

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query field data type (Access 2002)

    Not in the fashion you suggest. Are you wanting to sum produce one grand-total sum, or a sum of each numeric field? If the former, you must sum all of the numeric fields in one record, and then create a sum of the total. If you are wanting to sum each individual field and produce a record that contains each of the sums, a GroupBY query will do the trick, but it is limited to 10 fields. If you have that many fields, it suggests that your table design may not be optimal from a Microsoft Access perspective.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query field data type (Access 2002)

    Another words, I'm going to have to:
    SELECT SUM(field1) + SUM(field2) + SUM(field3) ...
    FROM TableWithWayTooManyNumbericFields

    Is that basically my only option?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query field data type (Access 2002)

    Pretty much that's right, but you may have problems with your approach if you have more than 10 fields to sum.
    The other approach is to sum individual fields in a record using and expression such as:
    SELECT field1 + field2 + field3 + field4 .... AS RecordSUM FROM TableWithWayTooManyNumericFields
    and then create a GROUPBY query based on that query which SUMs RecordSUM.
    Wendell

Posting Permissions

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