Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to add a column to my query that finds all the 1ís and then counts the number of 1ís and puts that result into the new column with the Header titled Jan.

    Iíve been searching all thought the web looking for some sort of IIF statement that might do what Iím asking, but having now luck. Any advice or help would be very much appreciated.

    If field1 has a 1 in move it to the new column header jan, if field1 has a 2 in it, mover it to a new column header Fed and so forth.

    If this helps, once I have all the new fields, Iíll need to count each on in a summary query

    Once again any advice or help would be very much appreciated.

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi!

    have you considered a Crosstab query?

    Regards,

    Jules

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I presume you are working with Access - if that's the case you can best do that on a report, not in a query. You can put a group heading into a report and include the count of records for each Month. You can use a GroupBy query to also do that sort of thing, but it will only show you the summary numbers, and not the detail records.
    Wendell

  4. #4
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do you do a groupby query?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    To create a Totals or GroupBy query you click the Totals button on the Query Design tab of the ribbon. (So you must be in the design view of a query to do this.)

    [attachment=90848:Totals.gif]

    You can read more about them here

    A crosstab query is a further step again. Crosstab is shown on the same ribbon tab.

    You can read about crosstabs here
    Attached Images Attached Images
    Regards
    John



  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Corey Vlasak View Post
    How do you do a groupby query?
    It depends on the version of Access you are working with - John has given you the answer for 2007/2010 - in earlier versions there is a Totals button available on the design toolbar, or as a command on the Menu.
    Wendell

  7. #7
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The Crosstab query worked great. thanks for the advice

Posting Permissions

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